How to escape XML characters in TSQL before converting to XML?
Use for xml path
to create XML instead of casting.
select @YourVariable for xml path(''), type
Empty string in the path expression and the absence of an alias on the returned column will give you your string back as xml.
That is perfectly valid XML. If you extract the bbb
element using the .value
method of the XML data-type, it will be de-entitised at that point, eg
You can extract and add the element with no special handling or replacing, eg using the .modify
method of the XML data-type:
DECLARE @xml XML = '<zzz xmlns="http://example.com">
<aaa>aaa</aaa>
<bbb>param1=xyz&para2=dasdasdfdas&param3</bbb>
</zzz>'
SELECT @xml [before], DATALENGTH(@xml) dl
DECLARE @b VARCHAR(100)
;WITH XMLNAMESPACES ( DEFAULT 'http://example.com' )
SELECT @b = @xml.value('(zzz/bbb/text())[1]', 'VARCHAR(100)')
SELECT @b
SET @xml.modify('declare default element namespace "http://example.com"; insert element bbb { sql:variable("@b") } after (zzz/bbb)[1]')
SELECT @xml [after], DATALENGTH(@xml) dl