Useful Things to Know, #427

When it comes to XML, SQL Server won't always eat its own cooking.

What I mean by that is that it's quite possible to generate syntactically valid XML from valid SQL Server data, via SQL Server queries, that is semantically invalid.  SQL Server will deliver this data, but it won’t consume it or allow it to be assigned to an XML-type variable.  

Here's how.  Suppose you have the bad luck to have some non-printable characters in some of your text fields. XML only tolerates a few non-printable characters -- although I'd have to look it up, I think the ASCII characters for 9, 10, and 13 are the only ones it does tolerate.

So consider this SQL:

create table BadChars
(
TextData varchar(255)
)
insert into BadChars
values
  (char(4)) -- bad char
, (char(6)) -- bad char
, (char(10)) -- good non-printable char
, (char(13)) -- good non-printable char
, (char(33)) -- regular character

Let's retrieve this data as XML:

select *
from BadChars
for xml auto, root('BadCharTable')

This will run fine, and return you a chunk of XML in SSMS, like so:

<BadCharTable>
  <BadChars TextData="&#4;">
  <BadChars TextData="&#6;">
  <BadChars TextData="&#10;">
  <BadChars TextData="&#13;">
  <BadChars TextData="!">
<BadCharTable>

Just try doing anything with it, though:

declare @xml Xml
select @xml =
(select *
 from BadChars
 for Xml auto, root('BadCharTable')
)

The error you'll get is this:

Msg 9420, Level 16, State 1, Line 3
XML parsing: line 1, character 40, illegal xml character

That's because the XML it so happily generated a few moments ago is not valid, due to those non-printable characters, that even in escaped form are not allowed.

So SQL Server will return invalid XML in a result set, if the source data contains invalid chars. It just won't consume it, or allow it to be assigned to an XML variable.