CODEWITHSUNDEEP
sql-serverxml
user4650842
user4650842

Reputation:

SQL Server 2008 Xml Issue With Xml Escape Characters

Our current Point of Sale system executes too many queries in nested transactions that leave duplicated or partial data in place. I changed the entire thing to a single stored procedure where all sale item data is passed in as Xml, iterated through in a temp table, and saved to the database, then committed. However, SQL rejects special characters in the xml.

For example:

 <?xml version="1.0" encoding="utf-16"?>
 <list>
     <item>
         <objectid>bd99fcb6-3031-48b7-9a71-5f8cefe0a614</objectid>
         <amount>50.00</amount>
         <fee>1.50</fee>
         <waivedfee>0.00</waivedfee>
         <tax>0.00</tax>
         <name>TEST &amp; TEST PERSON</name>
         <payeeid>197</payeeid>
         <accountnumber>5398520352</accountnumber>
         <checknumber />
         <comedreceiptnumber />
         <isexpedited>0</isexpedited>
         <echeckrefnumber />
    </item>
</list>

Fails. It tells me that there is an illegal character where &amp; is located. I don't know why. It's escaped properly with &amp;. I can't find any solutions online, anywhere. Everywhere people tell me to replace & with &amp; - which is what I am doing!

Upvotes: 1

Views: 363

Answers (2)

user4650842
user4650842

Reputation:

I figured it out. UTF-16 is correct. That Xml is fine. There was a final piece of xml, the ledgers, that were just plain strings with no encoding and no escaping special characters. Once I corrected that it all worked.

Thanks for the help!

Upvotes: 1

Donal
Donal

Reputation: 32713

Use XML PATH(''), it will encode the special characters for you.

SELECT 'TEST & TEST PERSON' FOR XML PATH('')

Upvotes: 1

Related Questions