OK, what happened to my whitespace?

Question

Post reply

OK, what happened to my whitespace?

SDM

SSCommitted

Points: 1581
More actions
February 21, 2007 at 10:59 pm

#69860

I have a problem with XML handling. I'm sure there's a reason for it, but understanding it might point to a workaround for me.
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)?
February 26, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pete adams Valued Member Points: 52 More actions · Answer 1

Sad state of affairs - MS stole them . . .

refer to http://msdn2.microsoft.com/en-us/library/ms187367.aspx

Their reasoning - "For backwards compatibility, sp_xml_preparedocument collapses the CR (char(13)) and LF (char(10)) characters in attributes even if these characters are entitized."

Screwing with my data now and looking for an answer

Russel Loski SSCertifiable Points: 7059 More actions · Answer 2

You could wrap this as CDATA which throws out all formatting (note the <![[CDATA[ closed by ]]>.

declare @dd varchar(300), @doc int

select @dd = '<root ><myelement ><![CDATA[' + 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) '.')

exec sp_xml_removedocument @doc

Russel Loski, MCSE Business Intelligence, Data Platform

SDM SSCommitted Points: 1581 More actions · Answer 3

I was giving up all hope, and now 2 responses in a day.

Thanks to both respondents. Yeah, I'm having to do the CDATA wrap, but that now means more handling in the client code, which IMNSHO just shouldn't need to be done.