Notes on SQL

Introduction
When setting the value of an XML column, so-called ‘insignificant’ white space can be removed. Generally this is not an issue but if the white space is required there are a couple of ways to prevent this.

Test Environment
For this demonstration, a very simple piece of code will be used. It will set a varchar value with an xml-formatted string and then use CONVERT to set an XML variable to this.

Test Execution
The first example merely sets the varchar to the required string and then uses CONVERT, with default settings to set the XML variable. One of the XML elements has several space characters set within and with the settings used, this data will be truncated.

Listing 1: Loading varchar data into an XML column with default settings

declare @x xml  
declare  @s varchar(1000)  
SET @s = '<Cust ><Fname>John </Fname><Lname>Smith</Lname><BlankBit>        </BlankBit></Cust >'   
set @x =convert (xml, @s)  
select @x;
GO

Result from Listing 1

<Cust>
  <Fname>John </Fname>
  <Lname>Smith</Lname>
  <BlankBit />
</Cust

As seen in the resultant XML ‘BlankBit’ has been reduced to one start/end tag, because during the conversion that was seen as being empty.

To preserve the white space, CONVERT has a third paramter of ‘style’ and when set to 1, will preserve the white space.

Listing 1: Loading varchar data into an XML column with CONVERT parameter 3 set

declare @x xml  
declare  @s varchar(1000)  
SET @s = '<Cust ><Fname>John </Fname><Lname>Smith</Lname><BlankBit>        </BlankBit></Cust >'   
set @x =convert (xml, @s,1)  
select @x;
GO

Result from Listing 2

<Cust>
  <Fname>John </Fname>
  <Lname>Smith</Lname>
  <BlankBit>        </BlankBit>
</Cust>

With this CONVERT style set, the white space is still there.

Another option is to specify within the XML that the spaces should be preserved. This can be done by specifying ‘xml:space=”preserve”‘ within the XML. The default behaviour is as if ‘xml:space=”default”‘ was specified, which removes white space in ’empty’ tags.

Listing 3: Loading varchar data into an XML column with xml:space “default”

DECLARE @x xml  
declare  @s varchar(1000)  
SET @s = '<root xml:space="default"><Cust ><Fname>John </Fname><Lname>Smith</Lname><BlankBit>        </BlankBit></Cust ></root>'   
set @x =convert (xml, @s)  
select @x;
GO

Result from Listing 3

<root xml:space="default">
  <Cust>
    <Fname>John </Fname>
    <Lname>Smith</Lname>
    <BlankBit />
  </Cust>
</root>

The behaviour of the listing above resulted in data very similar to Listing 1.

Listing 4: Loading varchar data into an XML column with xml:space “preserve”

declare @x xml  
declare  @s varchar(1000)  
SET @s = '<root xml:space="preserve"><Cust ><Fname>John </Fname><Lname>Smith</Lname><BlankBit>        </BlankBit></Cust ></root>'   
set @x =convert (xml, @s)  
select @x ;
GO

Result from Listing 4

<root xml:space="preserve">
  <Cust>
    <Fname>John </Fname>
    <Lname>Smith</Lname>
    <BlankBit>        </BlankBit>
  </Cust>
</root>

The behaviour of the listing above resulted in data very similar to Listing 2, with the white space preserved.

Combining the CONVERT ‘style’ parameter and the xml:space setting does not mean that CONVERT will override xml:space:
Listing 5: Loading varchar data into an XML column with xml:space “default” and CONVERT style 1

declare @x xml  
declare  @s varchar(1000)  
SET @s = '<root xml:space="default"><Cust ><Fname>John </Fname><Lname>Smith</Lname><BlankBit>        </BlankBit></Cust ></root>'   
set @x =convert (xml, @s,1)  
select @x;
GO

Result from Listing 5

<root xml:space="default">
  <Cust>
    <Fname>John </Fname>
    <Lname>Smith</Lname>
    <BlankBit />
  </Cust>
</root>

In the example above, the action of xml:space takes precedence over the setting of CONVERT, so the white space was removed.

References
Create Instances of XML Data
Cast and Convert