CODEWITHSUNDEEP
sql-serverxmlt-sqlstored-procedures
SBB
SBB

Reputation: 8970

TSQL Converting Characters in XML

I am generating an XML string in my Code that I send to my stored procedure. Due to the particular data containing an ampersand & I have to convert that to something else before sending the XML string as it messes with the XML structure.

I convert any instance of & to ~ in the XML string.

Normally, in the past I have re-converted that character once I'm about to add the data to the field and its worked fine.

However, this time around I am inserting a node of XML into a field and that character is contained with in that node.

 INSERT INTO TFS_Feedback_New (submitTime, type, reasonID, status, outcome, Details, submitterQID, submitterSupQID, submitterDepartment, submitterGeoLocationDesc, targetQID, targetSupQID, targetDepartment, targetGeoLocationDesc, feedbackOriginal)
SELECT GETUTCDATE(),
       @feedbackType,
       @feedbackReason,
       @status,
       'Pending',
       @xml.query('/data/optional'),
       @submitterQID,
       @SubmitterSupQID,
       @SubmitterDept,
       @SubmitterLoc,
       @TargetQID,
       @TargetSupQID,
       @TargetDept,
       @TargetLoc,
       @feedback

As yo u can see, there is a field @xml.query('/data/optional'). That is what contains the XML string that has the character I need to replace.

Here is the XML I send to the stored procedure <optional><fields><department>Dogs ~ Cats</department></fields></optional>

Before inserting that XML string into the field, I need to convert the ~ to a &.

Any ideas?

Upvotes: 2

Views: 1616

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48864

You need to do proper XML entity encoding. The bare-minimum you need to do is:

  • & = &amp;
  • < = &lt;

These are the only two that cause errors when converting to XML:

SELECT CONVERT(XML, '<tag><</tag>');

results in:

Msg 9455, Level 16, State 1, Line 1
XML parsing: line 1, character 7, illegal qualified name character

and

SELECT CONVERT(XML, '<tag>&</tag>');

results in:

Msg 9421, Level 16, State 1, Line 1
XML parsing: line 1, character 7, illegal name character

IF you are doing Attribute-based XML instead of Element-based, then you will also need to translate the double-quote:

  • " = &quot;

This is due to the possibility of it being placed in an attribute such as:

<tag attribute="test &quot; attr">Some stuff here</tag>

If you don't have any attributes then translating the double-quote is optional.

You can also do the following, but they won't cause errors if you don't:

  • > = &gt;
  • ' = &apos;

NOTE: Be sure to encode the & first, and then <, and then maybe " and possibly >. If you don't do & first then you will end up double-encoding the other entities that were encoded before it, resulting in something like &amp;lt; instead of just &lt;.

And since you mentioned building the XML in the app code, assuming you are using .NET, you can use the following method to do all of the above for you:

SecurityElement.Escape

Applying the above to the XML specified in the question, we get the following:

SELECT CONVERT(XML,
       '<optional><fields><department>Dogs &amp; Cats</department></fields></optional>'
              );

which converts successfully :).

Upvotes: 2

Andomar
Andomar

Reputation: 238196

The & character is not allowed in XML strings. It should be encoded as &amp;, for ampersand, or alternatively as &#038;, using the Unicode point for ampersand.

See if you can get your client to send & as properly encoded &amp; XML. I'd also suggest making your column of type xml, so you can rely on it containing syntactically correct XML.

Upvotes: 1

Related Questions