CODEWITHSUNDEEP
sql-serverxmlt-sqlxml-parsingspecial-characters
user3266033
user3266033

Reputation: 157

parsing character error in SQL via XML

I have the below code which errors when I run it because it has the "&" sign and can not convert it.

the result should display "testing &". however if I change the xml bit to "testing &" it works. I need a way to replace it so that it does not error.

Declare @Request XML = null
If @Request IS NULL 
BEGIN
    SET @Request = '
                    <Request>  
                       <ProductRequest>
                    <ProductName>testing &</ProductName>
                      </ProductRequest>
                     </Request>'
END 

select @Request.value ('(//ProductName)[1]','nvarchar(100)')

Upvotes: 2

Views: 92

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You are probably looking for this:

Declare @Request XML = null
If @Request IS NULL 
BEGIN
    SET @Request = (SELECT 'testing &' AS ProductName FOR XML PATH('ProductRequest'),ROOT('Request'));
END 

select @Request.value ('(//ProductName)[1]','nvarchar(100)')

Some background:

XML is more than just some text with extra characters. XML should never be generated just by typing (as in your case) or by string concatenation (often seen). Use the proper method to generate your XML and all encoding issues are solved for you implicitly.

Look at the XML generated and you will find, that the & is found as &amp;. While reading this with value() the re-encoding is done for you - again implicitly.

You should not start to do own REPLACE approaches. Next day someone enters a <or > or another not supported character and you have the same troubles again.

Upvotes: 2

Dan Guzman
Dan Guzman

Reputation: 46193

You need to specify the entity reference &amp; in the XML string for the ampersand:

DECLARE @Request XML = NULL;
IF @Request IS NULL 
BEGIN
    SET @Request = '
                    <Request>  
                       <ProductRequest>
                    <ProductName>testing &amp;</ProductName>
                      </ProductRequest>
                     </Request>';
END; 

SELECT @Request.value ('(//ProductName)[1]','nvarchar(100)');

See https://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

Upvotes: 0

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

The & is a reserved/special character in XML. It should be &amp ; and remove space between &amp and ;

as the next:

Declare @Request XML = null
If @Request IS NULL 
BEGIN
    SET @Request = '
                    <Request>  
                       <ProductRequest>
                    <ProductName>testing &amp;</ProductName>
                      </ProductRequest>
                     </Request>'
END 

select @Request.value ('(//ProductName)[1]','nvarchar(100)')

Upvotes: 1

Related Questions