Sql-server – SQL server changes XML structure when inserted

sql serverxml

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?

Best Answer

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;
Related Question