How to escape XML characters in TSQL before converting to XML?

Question

I have XML structure which is input param for my stored procedure. It contains element with & (which is escaped in XML). When I extract that element to VARCHAR I receive & which is not valid XML character. I need to escape it, before converting to XML again. How to do that without REPLACE?

I have the following text: param1=xyz&para2=dasdasdfdas&param3. It is a part of query string. I converted it to XML and send it as a part of XML structure:

<zzz xmlns="http://example.com">
  <aaa>aaa</aaa>
  <bbb>param1=xyz&amp;para2=dasdasdfdas&amp;param3</bbb>
</zzz>

Inside the stored procedure I need to extract it. I do that with:

ISNULL(NULLIF(LTRIM(RTRIM(@XMLInput.value('declare default element namespace "http://example.com"; (zzz/bbb)[1]', 'NVARCHAR(250)'))), ''), '');

I after that the value contains normal text (&-s are not escaped - &).

After some processing I need to put that string inside other XML. I make:

CAST( ... AS XML);

Because &-s are not escaped I got error.

Answer 1

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.

Answer 2

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&amp;para2=dasdasdfdas&amp;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