如何在T-SQL中的XML字符串的属性中转义双引号?

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.

我已经将@xml变量设置为xml类型和varchar(max)。

 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

4 个解决方案

#1

268

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

不,是“;在xml中?即。

"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)')

#2

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:

tSql用另一个双引号转义了双引号。如果你想让它成为sql字符串的一部分你可以这样做:

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:

如果您想在xml本身的值中包含一个引号，您可以使用一个实体，它应该如下所示:

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

#3

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.

不能再评论了，但投票表决，并想让人们知道“;当在Solr中为RegexTransformer生成regex表达式时，对xml配置文件非常有效:regex="。* img src =“(. *)“。*“使用转义版本而不是双引号。

#4

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;

#1

268