FOR XML AUTO, ELEMENTS Problem

I have a column ('ProblemResolution') in a table ('Incident') that holds plain text. I am doing a query against that column and converting the results to XML as follows:

Select ProblemResolution From Incident Where RowID = 2 FOR XML AUTO, ELEMENTS

The problem is the XML that is being generated. It appears the XML that is generated is illegal (in some cases) because if I save the resulting XML in a text file and load into Internet Explorer, IE generates errors.

Here is the plain text (actually part of it - enough to demo the problem) as stored in the column. The quotes are not stored.

"8/11/2006 dabonder -
Carol –

Thanks for the detail. I looked at the 6060 transaction and it was as you thought – these accounts are not set up in the .|
If the corresponding project account to 6060 would never be used in a time sheet or expense report then you would not need to have a.
I haven't had time to clarify this. If you want to discuss when you get time I would be happy to.

-D Abonder

D Abonder
Director of Consulting
Some Company
www.SomeCompany.com

Email: dgonder@.somecompany.com
Phone: 123-555-3450"

Here is the generated (illegal) XML:

<Incident><ProblemResolution>8/11/2006 dabonder -&#x0D;
&#x0D;
Carol –&#x0D;
&#x0D;
Thanks for the detail. I looked at the 6060 transaction and it was as you thought – these accounts are not set up in the .&#x0D;
&#x0D;
If the corresponding project account to 6060 would never be used in a time sheet or expense report then you would not need to have a.&#x0D;
&#x0D;
I haven&apos;t had time to clarify this. If you want to discuss when you get time I would be happy to.&#x0D;
&#x0D;
-D Abonder&#x0D;
&#x0D;
D Abonder&#x0D;
Director of Consulting&#x0D;
Some Company&#x0D;
www.SomeCompany.com&#x0D;
&#x0D;
Email: dgonder@.somecompany.com&#x0D;
Phone: 123-555-3450</ProblemResolution></Incident>

The problem is that the document contains invalid U+0000 characters that are not allowed in XML. FOR XML does not mark them as errors but outputs them anyway. You should clean your data in the table or when running your FOR XML expression or before passing the XML to the parser and remove the U+0000 code point or replace the &#x0D; with the zero-length string.

Best regards

Michael