MS SQL XML - FOR XML output - strange behavior

Author

FOR XML output - strange behavior

J Justin

2006-10-25, 6:06 am

We have an application that executes a SQL statement
SELECT * FROM tablename FOR XML AUTO, ELEMENTS and returns the data as XML.
However, we are running into the issue where after 2048 characters a carriage
is inserted in the output and thus the XML ends up not being valid.

Is it a known issue? Is there any workaround?

We have SQL Server 2000 Entp edition with SP4.

Thanks for any input.

J Justin

Kent Tegels

2006-10-25, 6:06 am

Hello J,

As far as I recall, no, there's no particular issue with this if you're using
a tool that reads it all as byte stream. Are you sure the data in question
doesn't have a return? How are you reading the data?

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

J Justin

2006-10-25, 6:06 am

Thanks Kent. I checked my data again. You are right. Carriage return is also
stored on couple of rows. If I exclude those rows in the WHERE clause, then
all are working fine. A custom developed web service is using this data.

How to make sure that SQL query with FOR XML statement will return all data
without issues regardless of whether carriage return is present in a row or
not?

J Justin

"Kent Tegels" wrote:

> Hello J,
>
> As far as I recall, no, there's no particular issue with this if you're using
> a tool that reads it all as byte stream. Are you sure the data in question
> doesn't have a return? How are you reading the data?
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>
>

Kent Tegels

2006-10-25, 6:06 am

Hello J,

a.) clean up the existing data to remove the returns
b.) enforce good data validation on entry to not allow returns
c.) consider using an FOR XML EXPLICT query to emit the questionable field
as CDATA (I think this is possible)

kt

Sponsored Links