I am having a problem with XML and white space (again), but this time my
white space is being ignored, and I want it to be there.
I'm not positive where the problem lies (CF or SQL), but I'm afraid it
might be the SQL side.
Consider this XML string:
<accounting_batch_layout>
<accounting_batch_layout_column static_content=" gsdfgsfdg
dfgfsdg " />
</accounting_batch_layout>
Note the static_content attribute has a value of " gsdfgsfdg dfgfsdg
"
We pass this XML into a stored proc (MS SQL 2005) as an varchar
datatype.
Then the proc uses openxml to insert the data into the database.
The static_content value is stored (in a varchar column) as "gsdfgsfdg
dfgfsdg". Note all white space has been trimmed from the beginning and
end of the string.
This example seems to show the behavior in Query Analyzer:
DECLARE @xml_data varchar(max)
DECLARE @idoc AS int
SET @xml_data = '
<accounting_batch_layout>
<accounting_batch_layout_column static_content="
gsdfgsfdg dfgfsdg " />
</accounting_batch_layout>'
-- Found this on Google, but it appears to ONLY preserve whitespace
BETWEEN tags.
--set @xml_data = convert(xml, @xml_data,1)
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml_data
SELECT '*' + static_content + '*' FROM OPENXML
(@idoc,'/accounting_batch_layout/accounting_batch_layout_column') WITH
(static_content varchar(255))
EXEC sp_xml_removedocument @idoc
convert(xml, @xml_data,1) is supposed to make white space be preserved,
but it appears to ONLY apply to white space between the tags.
My white space is in the actual value of the attribute.
~Brad
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286976
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
