Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Whitespace stripped from XML - why?

Author  Topic  SDM Starting Member 1 Post Posted - 2007-02-28 : 18:34:28 I have a problem with XML handling. I'm sure there's a reason for it, and understanding it might point me to a workaround. I'm sure I'm not the first to have run into this. I've read a bit about how the various document processors treat insignificant whitespace (including CR, LF) but the documented issues seem to be about "insignificant", semantically neutral whitespace - say inserted between elements for readability - not within the element value. Consider the following : declare @dd varchar(50), @doc int select @dd = '<root><myelement>' + char(13) + char(10) + 'some text' + char(13) + char(10) + '</myelement></root>' select @dd -- OK, CRLF is definitely there either side of the text exec sp_xml_preparedocument @doc output, @dd select '*' + nodeval +'*' -- visible characters around what's being returned from OPENXML (@doc,'/',3) with (NodeVal varchar(100) '.') Now I was expecting to get '* some text *' but instead I get '*some text*' Is there some explanation for this? Is there any way to preserve the CRLF (or other leading or trailing whitespace)? N.B. This behaviour is the same on both 2000 and 2005