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">

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.


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">

SELECT @xml [before], DATALENGTH(@xml) dl


;WITH XMLNAMESPACES ( DEFAULT 'http://example.com' )
SELECT @b = @xml.value('(zzz/bbb/text())[1]', 'VARCHAR(100)')


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.