Preserving Whitespace FOR OPENXML

February 12, 2009 in Database
Tags: ,
Leave a Comment 

Using Microsoft SQL Server 2005, when you read xml data using FOR OPENXML, whitespace characters are replaced with a single space. After digging through the internet for half an hour, I found a couple options.

The Problem

If you take the following Xml,

<Root>
    <Node value="Line1&#xA;Line2&#xA;Line3&#xA;" />
</Root>

and run it through a FOR OPENXML query

DECLARE 
    @strXML nvarchar(MAX), 
    @hDoc int 

SET @strXML = N'<Root><Node value="Line1&#xA;Line2&#xA;Line3&#xA;" /></Root>'

exec sp_xml_preparedocument @hDoc output, @strXML

SELECT Node FROM OPENXML(@hDoc, '/Root') WITH (Node xml 'Node')

exec sp_xml_removedocument @hDoc

SQL strips/replaces the whitespace characters, leaving you with the following:

<Node value="Line1 Line2 Line3 " />

The Options

One option is to use the nodes() functionality. It is a complete alternative to using FOR OPENXML, introduced in SQL 2005. A couple of things deterred me from using this approach. Although performance is not going to be an issue for me, there seemed to be a number of related complaints. Further, I didn’t want to rewrite a significant portion of my codebase if it wasn’t necessary.

Instead, i simply pulled my data out of an xml attribute (i found no way to prevent FOR OPENXML from rewriting attribute whitepace) and stuck it in a CDATA.

SET @strXML = N'<Root><Node><![CDATA[
Line1
Line2
Line3
]]></Node></Root>'

Results:

<Node>
Line1
Line2
Line3
</Node>

Comments

Leave a Reply