1

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!

Improve this question
7
  • For reference, this didn't parse the "code" bbcode properly. I am escaping special characters properly. An ampersand is written as ampersand+amp+semi-colon as appropriate. Yet Sql is rejecting that as an illegal character. Commented Mar 9, 2015 at 17:38
  • I tried changing encoding to UTF-8. Interestingly enough, this works perfectly if I execute it in sql server management studio (declare blah blah = my xml, select from my xml) but fails from C#. Even more odd, Sql Server Profiler can't seem to capture the query so I can figure out what's going on. Commented Mar 9, 2015 at 18:09
  • you need to post your c# code Commented Mar 9, 2015 at 18:19
  • saleParameters.Add(new DbParam("@BC_Xml", BC_Xml != null ? BC_Xml : (object)(DBNull.Value), DbType.Xml)); Commented Mar 9, 2015 at 18:33
  • I've tried it as a string, with or without specifying datatype xml, with UTF-8, 16, ASCII, default (Windows-24 something) Commented Mar 9, 2015 at 18:34

2 Answers 2

Reset to default
1

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

SELECT 'TEST & TEST PERSON' FOR XML PATH('')
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks - but I'm not trying to construct xml in Sql Server. I am trying to find out why Sql is rejecting Xml written from an outside source and passed to the command as a SqlXml object.
@Darsithis it could be a character encoding issue or an illegal character. Also, what is the data type of the column in question?
Hi Donal. The data type is nvarchar(200). That fragment of xml is passed in as SqlXml to a parameter of XML. I iterate through all items as FROM @BC_xml.nodes('list/item') AS item([Item])
I suggest you use the XML data type, instead of nvarchar.
I can't change all of our sale tables to no longer use nvarchar. If you're referring to the parameter in the stored procedure, that is type XML
1

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!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.