parsing character error in SQL via XML

Question

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)')

Gottfried Lesigang · Accepted Answer · 2016-11-15 13:31:49Z

2

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 &. 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.

edited Nov 15, 2016 at 13:31

answered Nov 15, 2016 at 13:26

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ahmed abdelqader · Accepted Answer · 2016-11-15 13:15:57Z

1

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)')

answered Nov 15, 2016 at 13:15

ahmed abdelqader

3,58220 silver badges38 bronze badges

2 Comments

user3266033 Over a year ago

I need something to replace "&" with "&" automatically, as the user will only enter & in the productname area

Gottfried Lesigang Over a year ago

I'd strongly advise not even to think about entity references. This is something done for you implicitly, if you just use SELECT ... FOR XML ... When you deal with XML one should avoid to think in string...

Dan Guzman · Accepted Answer · 2016-11-15 13:16:10Z

0

You need to specify the entity reference & 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

answered Nov 15, 2016 at 13:16

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

2 Comments

Gottfried Lesigang Over a year ago

I'd strongly advise not even to think about entity references. This is something done for you implicitly, if you just use SELECT ... FOR XML ... When you deal with XML one should avoid to think in string...

Dan Guzman Over a year ago

@Shnugo, agreed entity references are transparent when XML objects and methods are used, but that is not the case here since the string was created manually.

Collectives™ on Stack Overflow

parsing character error in SQL via XML

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related