How do I escape double quotes in attributes in an XML String in T-SQL?
Solution 1
Wouldn't that be "
in xml? i.e.
"hi "mom" lol"
**edit: ** tested; works fine:
declare @xml xml
set @xml = '<transaction><item value="hi "mom" lol"
ItemId="106" ItemType="2" instanceId="215923801" dataSetId="1" /></transaction>'
select @xml.value('(//item/@value)[1]','varchar(50)')
Solution 2
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.
Solution 3
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 "mom" lol"" /></transaction>"
Solution 4
In Jelly.core to test a literal string one would use:
<core:when test="${ name == 'ABC' }">
But if I have to check for string "Toy's R Us":
<core:when test="${ name == &quot;Toy's R Us&quot; }">
It would be like this, if the double quotes were allowed inside:
<core:when test="${ name == "Toy's R Us" }">
Related videos on Youtube
Adam
Updated on September 24, 2020Comments
-
Adam almost 4 years
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
-
Marc Gravell over 15 yearsBy 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.
-
Adam over 15 yearsMarc - 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.
-
Amit Patil over 15 yearsBest not to use double-quotes as SQL string delimiters though. Single quotes are ANSI standard and always work, regardless of the QUOTED_IDENTIFIER setting.
-
Joel Coehoorn over 15 yearsAgreed, but I wanted to demonstrate that's it's possible, just in case there was any confusion about what he was trying to do.