Problem when saving XML results to a file

Hi all,

I'm using SQL Server 2005 Standard, and i'm trying to generate a large XML file to be used as an archive for a table I want to query for analysis/reporting. The problem is, when I save the query results as XML the file has many embedded \r\n in the middle of my data rows, causing this file to throw errors.

What could possibly be causing these characters to show up in the rows? They do not appear in the data, but rather in the field/property names like this:

QUERY:

Code Snippet

SELECT AccountNumber,TenantNumber,SequenceNumber,RecordType,Date,Reference,Code,ServiceCode,RateCode,
MeterNumber,Amount,BudgetBillAmount,Reading,DemandReading,Usage,DemandUsage,ServiceSequence,ReasonCode
FROM UBAccountHistory as dt
WHERE Date < '01/01/2007'
FOR XML AUTO, ROOT('rdData')

Returns something like:

Code Snippet

<rdData>

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="0" RecordType="1" Date="1901-01-01T00:00:00" Ref

erence="0" Code="0" ServiceCode="" RateCode="" MeterNumber="" Amount="0.00" BudgetBillAmount="0.00" Reading="0" DemandReading="0.0000" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="49" RecordType="2" Date="2005-12-02T00:00:00" Reference="2881" Code="5" Servi

ceCode="WA" RateCode="W41" MeterNumber="99990020" Amount="0.00" BudgetBillAmount="0.00" Reading="23817" DemandReading="2.3817" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

</rdData>

I'm completely baffled by this. Someone recommended I try the BCP utility to export to XML as opposed to saving to a file...any other thoughts?

Thanks!

Mike

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

|||

a guy named Mike wrote:

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

Code Snippet

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

Code Snippet

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

That is exactly what I was looking for. PERFECT! Thanks so much!

Cheers,

Mike