SQL server changes XML structure when inserted

Question

I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server. Here is the data I insert

              <xsl:value-of select="name/n/given" />
            <xsl:text> </xsl:text>
          <xsl:value-of select="name/n/family" />

When I read it back, it looks like this

              <xsl:value-of select="name/n/given" />
          <xsl:text />
          <xsl:value-of select="name/n/family" />

Pay attention to the second line. This is a problem because it changes how the XSLT transformation output will be. The first example will create a space between given and family name, while the second will not create any space, so it will be like JohnJohnsen, while the first one will be like John Johnsen.

Is there some way to solve this?

Answer 1

You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.

For one node

declare @X xml =
'<root>
  <element xml:space = "preserve"> </element>
  <element> </element>
</root>'

select @X;

Result:

<root>
  <element xml:space="preserve"> </element>
  <element />
</root>

Entire document:

declare @X xml =
'<root xml:space = "preserve">
  <element> </element>
  <element> </element>
</root>'

select @X;

Result:

<root xml:space="preserve">
  <element> </element>
  <element> </element>
</root>

Another options for the entire document is to use convert with style 1.

Preserve insignificant white space. This style setting sets the default xml:space handling to match the behavior of xml:space="preserve".

declare @X xml = convert(xml, 
'<root>
  <element> </element>
  <element> </element>
</root>', 1)

select @X;

Answer 2

This page of the SQL Server documentation says

The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.

For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.

Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:

create table x(i nvarchar(99), j xml);
insert x values ('<a> </a>', '<a> </a>');  -- note the space
select * from x

i           j
----------  -------
<a> </a>    <a />  

The nvarchar column preserves the input format, the XML column does not.

You will lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.

Answer 3

You could wrap your space within CDATA when storing the data:

<xsl:text><![CDATA[ ]]></xsl:text>

It appears that SQL server then keeps the space internally, but removes the unnecessary CDATA markup itself when getting the result back using SELECT. Fortunately, the space is kept when re-using the result of such a SELECT:

DECLARE @X XML = '<text><![CDATA[ ]]></text>'
DECLARE @Y XML

SET @Y = (SELECT @X)

SELECT @Y

The result will be:

<text> </text>