strange results in FOR XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

strange results in FOR XML

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-22 : 13:23:12

Anybody has an idea why I have an error in every 2nd or 3rd line of the resultset produced by the following query (execute mode is set to Results to file):

select HRACaseID,
Suffix,
LineNumber
from hracases
where hracaseid < 6000
for xml auto,elements

Here is the result:
<hracases><HRACaseID>5385</HRACaseID><Suffix>02</Suffix><LineNumber>02</LineNumber></hracases>
<hracases><HRACaseID>5386</HRACaseID><Suffix>6</Suffix><LineNumber>6</LineNumber></hracases>
<hracases><HRACaseID>5387</HRACaseID><Suffix>01</Suffix><LineNumber>01mber>01</LineNumber></hracases> !!!
<hracases><HRACaseID>5422</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases>
<hracases><HRACaseID>5423</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases>
<hracases><HRACaseID>5424</HRACaseID>RACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases> !!!
<hracases><HRACaseID>5444</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases>
<hracases><HRACaseID>5445</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases>
<hacases><hracases><HRACaseID>5465</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases> !!!
<hracases><HRACaseID>5466</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases>
<hracases><HRACaseID>5467</HRACaseID><Suffix>01</Suffix><Linefix><LineNumber>01</LineNumber></hracases> !!!
<hracases><HRACaseID>5487</HRACaseID><Suffix>01</Suffix><LineNumber>02</LineNumber></hracases>
<hracases><HRACaseID>5488</HRACaseID><Suffix>01</Suffix><LineNumber>03</LineNumber></hracases>
<hracases><HRACaseID>5489<eID>5508</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases> !!!
<hracases><HRACaseID>5509</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases>
<hracases><HRACaseID>5510</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></Number></hracases> !!!
<hracases><HRACaseID>5530</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases>
<hracases><HRACaseID>5531</HRACaseID><Suffix>01</Suffix><LineNumber>01</LineNumber></hracases>

All lines with errors marked with !!!

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 13:45:00

"execute mode is set to Results to file"

Are you using Query Analyser? if so it will insert a line-break at every 8,000 characters (or something like that)

You have to use a Stream to get XML out of SQL Server, a normal ResultSet type connection won't do ...

Kristen

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-22 : 13:57:04

Kristen,

You're right. I completely forgot about line-break.

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 14:15:32

There's a method of getting the XML out with BCP - in case you are not familiar with that route.

You get bucket-loads of warnings, but they are benign and the output is fine.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources