Wednesday, 7 January 2015

How can I assign an xml typed variable and take advantage of the automatic escaping of invalid characters?



How can I take advantage of sql server's automatic escaping invalid characters while assigning to an xml typed variable?


This query escapes and invalid character correctly.



SELECT TestCharacter "MyCharacter"
FROM (SELECT 'An element with normal text and an invalid character: ' + CAST(0x1B00 AS NVARCHAR(128)) AS TestCharacter
) MyTable
FOR XML AUTO ,ELEMENTS

<MyTable>
<MyCharacter>An element with normal text and an invalid character: &#x1B;</MyCharacter>
</MyTable>


This fails:



-- This fails:
DECLARE @xml XML = (
SELECT TestCharacter "MyCharacter"
FROM (SELECT 'An element with normal text and an invalid character: ' + CAST(0x1B00 AS NVARCHAR(128)) AS TestCharacter
) MyTable
FOR XML AUTO ,ELEMENTS
)

XML parsing: line 1, character 28, illegal xml character

No comments:

Post a Comment