如何在T-SQL中的XML字符串中的属性中避免双引号？ [英] How do I escape double quotes in attributes in an XML String in T-SQL?

问题描述

很简单的问题 - 我有一个属性，我想要双引号。我如何逃避他们？我试过

• \




• 
  

我已经为所有的xml类型和varchar（max）设置了@xml变量

 声明@xml xml  - （或varchar（max）尝试过）
 
 set @xml ='< transaction>< item value =himomlol
 ItemId =106ItemType =2instanceId =215923801dataSetId =1/> / transaction>'
 
声明@xh int 
 exec sp_xml_preparedocument @xh OUTPUT，@xml 
 
插入到@commits  - 我声明表，刚刚删除它为简洁起见
从openxml（@xh，'/ transaction / item'）中选择
 x。* 
 
 WITH（
 dataItemId int，
 dataItemType int ，
 instanceId int，
 dataSetId int，
 value varchar（max）
）x 
  

解决方案

在xml中不会是& quot; 即

 hi& quot;妈& quot; lol
  pre> 
 
 
 **编辑：**测试;工作正常：
  declare @xml xml 
 
 set @xml ='< transaction>< item value =hi&mom& quot; lol
 ItemId =106ItemType =2instanceId =215923801dataSetId =1/>< / transaction>'
 
 select @ xml.value（'（// item / @ value）[1]'，'varchar（50）'）
  
 
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

解决方案
Wouldn't that be &quot; 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)')


                        
这篇关于如何在T-SQL中的XML字符串中的属性中避免双引号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！