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.
If you take the following Xml,
<Root> <Node value="Line1
" /> </Root>
and run it through a FOR OPENXML query
DECLARE @strXML nvarchar(MAX), @hDoc int SET @strXML = N'<Root><Node value="Line1
" /></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 " />
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>'
<Node> Line1 Line2 Line3 </Node>