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     lt transaction gt  lt item value  hi  mom  lol       ItemId  106   ItemType  2   instanceId  215923801   dataSetId  1    gt  lt  transaction gt     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

User · Answer

In Jelly core to test a literal string one would use    amp lt core when test     name     ABC     amp gt     But if I have to check for string  Toy s R Us     amp lt core when test     name     amp amp quot Toy amp apos s R Us amp amp quot     amp gt    It would be like this  if the double quotes were allowed inside    amp lt core when test     name     Toy s R Us     amp gt

User · Answer

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

User · Answer

Wouldn t that be  amp quot  in xml  i e    hi  amp quot mom amp quot  lol       edit     tested  works fine   declare  xml xml   set  xml     lt transaction gt  lt item value  hi  amp quot mom amp quot  lol       ItemId  106   ItemType  2   instanceId  215923801   dataSetId  1    gt  lt  transaction gt    select  xml value     item  value  1    varchar 50

User · Answer

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     lt transaction gt  lt item value   hi     gt  lt  transaction gt     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     lt transaction gt  lt item value   hi  amp quot mom amp quot  lol     gt  lt  transaction gt

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

Examples related to xml

Examples related to tsql

Examples related to escaping