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?

  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen – Mr Zach Nov 27 '18 at 1:26
  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it) – a_vlad Nov 27 '18 at 1:31
  • 1
    Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time. – Aaron Bertrand Nov 27 '18 at 1:42

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 =
  <element xml:space = "preserve"> </element>
  <element> </element>

select @X;


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

Entire document:

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

select @X;


<root xml:space="preserve">
  <element> </element>
  <element> </element>
  • Interesting that this is required. It shouldn't be SQL Server's remit to decide what whitespace is "insignificant" and silently strip it without document modifications! – Lightness Races in Orbit Nov 27 '18 at 10:39
  • 2
    @LightnessRacesinOrbit I'm quite happy with the implementation by SQL Server. Formatting (whitespace) in XML is not considered important until you say it is. Have a look at this example to see the number of nodes that are actually in the document and what it does to storage size.. – Mikael Eriksson Nov 27 '18 at 12:33
  • 3
    I consider it to be a spec violation, because here the data is accepted as XML and stored as XML, with no manipulation or transformation or any other form of XML-layer shenanigans other than simply storing the document (ostensibly), so the behaviour should fall into that of a "processor" rather than an "application", and therefore must not strip whitespace. – Lightness Races in Orbit Nov 27 '18 at 12:58

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.

  • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there. – Aaron Bertrand Nov 27 '18 at 2:49

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>'



The result will be:

<text> </text>
  • Also tried CDATA but it was also removed. – Mr Zach Nov 27 '18 at 16:45
  • @MrZach CDATA itself is removed, but the space remains. (Tried on SQL Express 2016.) – Bruno Nov 27 '18 at 16:46
  • Strange, here the space was removed. Think also express 2016 or 2017 – Mr Zach Nov 27 '18 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.