How do I escape double quotes in attributes in an XML String in T-SQL?

Question

Pretty simple question - I have an attribute that I would like to have double quotes in. How do I escape them? I've tried

\"
""
\\"

And I've made the @xml variable both xml type and varchar(max) for all of them.

 declare @xml xml --(or varchar(max) tried both)

 set @xml = '<transaction><item value="hi "mom" lol" 
    ItemId="106"  ItemType="2"  instanceId="215923801"  dataSetId="1" /></transaction>'

 declare @xh int
 exec sp_xml_preparedocument @xh OUTPUT, @xml

 insert into @commits --I declare the table, just removed it for brevity
 select
    x.*
 from openxml(@xh,'/transaction/item')
  WITH (
    dataItemId int,
     dataItemType int,
    instanceId int,
    dataSetId int,
    value varchar(max)
  ) x

By the way... there is no reason (AFAIK) to use openxml here... that is "pre-2005" stuff. If you have an xml value, use it as xml directly. — Marc Gravell, Mar 16 '09 at 15:15
Marc - Thank you. I had another bug that ended up being openxml was busting with curly apostrophes: ’ I think I'm going to post it as a question/answer for google to find. — Tom Ritter, Mar 17 '09 at 20:34

score 275 · Accepted Answer · answered Mar 16 '09 at 15:10

Wouldn't that be " in xml? i.e.

"hi &quot;mom&quot; lol"

**edit: ** tested; works fine:

declare @xml xml

 set @xml = '<transaction><item value="hi &quot;mom&quot; lol" 
    ItemId="106"  ItemType="2"  instanceId="215923801"  dataSetId="1" /></transaction>'

select @xml.value('(//item/@value)[1]','varchar(50)')

score 4 · Answer 2 · answered Mar 16 '09 at 15:14

4

tSql escapes a double quote with another double quote. So if you wanted it to be part of your sql string literal you would do this:

declare @xml xml 
set @xml = "<transaction><item value=""hi"" /></transaction>"

If you want to include a quote inside a value in the xml itself, you use an entity, which would look like this:

declare @xml xml
set @xml = "<transaction><item value=""hi &quot;mom&quot; lol"" /></transaction>"

answered Mar 16 '09 at 15:14

Joel Coehoorn

399,467
113
570
794

4

Best not to use double-quotes as SQL string delimiters though. Single quotes are ANSI standard and always work, regardless of the QUOTED_IDENTIFIER setting. – bobince Mar 17 '09 at 06:49
Agreed, but I wanted to demonstrate that's it's possible, just in case there was any confusion about what he was trying to do. – Joel Coehoorn Mar 17 '09 at 13:43

score 4 · Answer 3 · answered Sep 14 '11 at 19:52

Cannot comment anymore but voted it up and wanted to let folks know that " works very well for the xml config files when forming regex expressions for RegexTransformer in Solr like so: regex=".*img src="(.*)".*" using the escaped version instead of double-quotes.

score 2 · Answer 4 · edited Sep 23 '13 at 13:04

2

In Jelly.core to test a literal string one would use:

&lt;core:when test="${ name == 'ABC' }"&gt;

But if I have to check for string "Toy's R Us":

&lt;core:when test="${ name == &amp;quot;Toy&apos;s R Us&amp;quot; }"&gt;

It would be like this, if the double quotes were allowed inside:

&lt;core:when test="${ name == "Toy's R Us" }"&gt;

edited Sep 23 '13 at 13:04

oHo

51,447
27
165
200

answered Nov 18 '10 at 19:54

Mark

29
2

How do I escape double quotes in attributes in an XML String in T-SQL?

4 Answers4

Linked