FOR XML EXPLICIT and XML HEADER

I have a FOR XML EXPLICIT
statement that is working correctly in every way except for the VERSION
header at the very top.
<?xml version="1.0" encoding="utf-8"?>
Is there anyway to get SQL Server to put this into the returned results?
Thank you
Chris
Since version 1.0 is the default and the encoding is implied by the
resulting codepage, FOR XML itself does not add any of that. You however
should be able to set the encoding through the client-side API (such as the
ADO command stream object).
Best regards
Michael
"Chris Alm" <none@.none.com> wrote in message
news:%23SiV0W3aFHA.2180@.TK2MSFTNGP12.phx.gbl...
>I have a FOR XML EXPLICIT
> statement that is working correctly in every way except for the VERSION
> header at the very top.
> <?xml version="1.0" encoding="utf-8"?>
> Is there anyway to get SQL Server to put this into the returned results?
> Thank you
> Chris
>

FOR XML EXPLICIT and XML HEADER

I have a FOR XML EXPLICIT
statement that is working correctly in every way except for the VERSION
header at the very top.
<?xml version="1.0" encoding="utf-8"?>
Is there anyway to get SQL Server to put this into the returned results?
Thank you
ChrisSince version 1.0 is the default and the encoding is implied by the
resulting codepage, FOR XML itself does not add any of that. You however
should be able to set the encoding through the client-side API (such as the
ADO command stream object).
Best regards
Michael
"Chris Alm" <none@.none.com> wrote in message
news:%23SiV0W3aFHA.2180@.TK2MSFTNGP12.phx.gbl...
>I have a FOR XML EXPLICIT
> statement that is working correctly in every way except for the VERSION
> header at the very top.
> <?xml version="1.0" encoding="utf-8"?>
> Is there anyway to get SQL Server to put this into the returned results?
> Thank you
> Chris
>

FOR EACH Loop to export files based on Header Record

Hey guys,,

Well im new at this SSIS stuff and i have something that i am trying to do, but cannot get it to work.. Ill try to explain, and if anyone can help me or point me in the right direction it would be much appriciated..

I have 2 tables, one header table. and one lines table. This is a one - to -many relationship.. ie 1 header, many lines.. This is a Order Header, and Order Lines table setup.. Order header has Order numbers and and email address field that link to the lines table by order number. I also have a view which links all this info together.

I would like to export a excel file (preferable named from the order number column - but that can come later) for each order number in the header table. The excel file will contain the details from the View that was created. I want this to loop through all the header records in the header table and create a excel file for each one..

Down the track i want to add a send mail task to this and pass the email address to a variable so i can use it in the send mail task.. But ill get the main part working first..

Anyhelp would be more than helpful.. I tried to set this up, but i am stuck on the enumerator part..

thanks again, scotty

I just created my first blog entry with something for you; let me know if it helps.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

Thanks Rafael.. This is exactly what i wanted to do and you have described the process exactly

Much appriciated..

Merry xmas and have a happpy new year..

Thaks

Scotty

Footer without header on final page

Hello. I have googled for answers to this problem, but while finding
posts that seemed similar to my problem no solution has been found.
I have a report that contains a table header and footer. I want the
header (the column headers) to appear on each page, and the footer (a
totals row) to appear once at the end of the report.
So I have the table properties RepeatFooterOnNewPage set to false and
RepeatHeaderOnNewPage set to true. For most cases this works as I want
it.
Sometimes the table has a number of rows such that the footer goes on
a page by itself, without the header. Any help fixing this problem
would be much appreciated. Either getting the header to go on the
final page with the footer, or perhaps fiddling the page breaking so
this situation doesn't arise.
Thanks very much,
GregOops, forgot to say that I've only seen it happen in the PDF export.

Footer rows to skip

OK. We know there is Header rows to skip options and it works great.

I've got the file that has a "footer". Here is an example:

.
PSC
filename=table1
records=0000000000525
ldbname=db1
timestamp=2006/02/07-16:25:00
numformat=44,46
dateformat=mdy-1910
map=NO-MAP
cpstream=ISO8859-1
.
0000260611

It's ALWAYS last 12 rows.

Is there a way to split at this point and put the 12 rows in a different location? The task is twofold - I don't need these control rows in my data and I need value of "records" to verify loaded number of rows.

UPDATED: After some testing I found out that the Flat File source does not see that footer at all. This is good and bad - I do want to load this metedat into some other tables.

Dima.

I too have the same type of flat files.

If u have solved this issue. Do help me please.

|||You can use another Data Flow to read the file a second time, with each row as a single column. Use a conditional split to identify the header/footer rows that you're interested in and derived columns (or script if necessary) to do any parsing before sending them to a destination. The downside here is that you have to read the file twice.

Alternatively, you can tackle the whole job in a single Data Flow with a script component that accepts the file rows as a single column, parses them in code, outputs the detail rows in discrete columns, and does whatever necessary for the header and footer. This is slightly more complicated from a code standpoint and the parsing logic could be a lot more tedious than letting the Flat File Source component do it.
|||Or perhaps read the file one time to get the number of rows in a variable and add a rownumber column to each row. Then in a second data flow use a conditional split to redirect the footer rows. This should work if you have a fixed number of rows in the footer.|||Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

|||

killerless wrote:

Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

Yeah. Read the other posts. And you can use a script component in the data flow to assign a value from the footer to a variable to that you can use it in your sanity check.

|||Sorry, Thank you.

Footer rows to skip

OK. We know there is Header rows to skip options and it works great.

I've got the file that has a "footer". Here is an example:

.
PSC
filename=table1
records=0000000000525
ldbname=db1
timestamp=2006/02/07-16:25:00
numformat=44,46
dateformat=mdy-1910
map=NO-MAP
cpstream=ISO8859-1
.
0000260611

It's ALWAYS last 12 rows.

Is there a way to split at this point and put the 12 rows in a different location? The task is twofold - I don't need these control rows in my data and I need value of "records" to verify loaded number of rows.

UPDATED: After some testing I found out that the Flat File source does not see that footer at all. This is good and bad - I do want to load this metedat into some other tables.

Dima.

I too have the same type of flat files.

If u have solved this issue. Do help me please.

|||You can use another Data Flow to read the file a second time, with each row as a single column. Use a conditional split to identify the header/footer rows that you're interested in and derived columns (or script if necessary) to do any parsing before sending them to a destination. The downside here is that you have to read the file twice.

Alternatively, you can tackle the whole job in a single Data Flow with a script component that accepts the file rows as a single column, parses them in code, outputs the detail rows in discrete columns, and does whatever necessary for the header and footer. This is slightly more complicated from a code standpoint and the parsing logic could be a lot more tedious than letting the Flat File Source component do it.
|||Or perhaps read the file one time to get the number of rows in a variable and add a rownumber column to each row. Then in a second data flow use a conditional split to redirect the footer rows. This should work if you have a fixed number of rows in the footer.|||Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

|||

killerless wrote:

Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

Yeah. Read the other posts. And you can use a script component in the data flow to assign a value from the footer to a variable to that you can use it in your sanity check.

|||Sorry, Thank you.

Footer rows to skip

OK. We know there is Header rows to skip options and it works great.

I've got the file that has a "footer". Here is an example:

.
PSC
filename=table1
records=0000000000525
ldbname=db1
timestamp=2006/02/07-16:25:00
numformat=44,46
dateformat=mdy-1910
map=NO-MAP
cpstream=ISO8859-1
.
0000260611

It's ALWAYS last 12 rows.

Is there a way to split at this point and put the 12 rows in a different location? The task is twofold - I don't need these control rows in my data and I need value of "records" to verify loaded number of rows.

UPDATED: After some testing I found out that the Flat File source does not see that footer at all. This is good and bad - I do want to load this metedat into some other tables.

Dima.

I too have the same type of flat files.

If u have solved this issue. Do help me please.

|||You can use another Data Flow to read the file a second time, with each row as a single column. Use a conditional split to identify the header/footer rows that you're interested in and derived columns (or script if necessary) to do any parsing before sending them to a destination. The downside here is that you have to read the file twice.

Alternatively, you can tackle the whole job in a single Data Flow with a script component that accepts the file rows as a single column, parses them in code, outputs the detail rows in discrete columns, and does whatever necessary for the header and footer. This is slightly more complicated from a code standpoint and the parsing logic could be a lot more tedious than letting the Flat File Source component do it.
|||Or perhaps read the file one time to get the number of rows in a variable and add a rownumber column to each row. Then in a second data flow use a conditional split to redirect the footer rows. This should work if you have a fixed number of rows in the footer.|||Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

|||

killerless wrote:

Has anyone figured out how to look at the footer rows? The footer row contains the number of rows in the file, and I'd like to use this as a sanity check. Thanks in advance.

Yeah. Read the other posts. And you can use a script component in the data flow to assign a value from the footer to a variable to that you can use it in your sanity check.

|||Sorry, Thank you.