查询 xml 数据 - 来自 Microsoft 的示例数据不起作用

我正在寻找一种无需手动编辑文件或复制粘贴内容的解决方案.我正在尝试使用此方法来查询 xml 文档

声明@myDoc xml声明 @ProdID intSET @myDoc = '<ProductDescription ProductID="1";ProductName=公路自行车"><特点><保修>1 年部件和人工</保修><维护>提供 3 年部件和人工延长维护</Maintenance></功能></产品描述></Root>'SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )选择@ProdID

示例来自 https://docs.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver15#a-using-the-value-method-against-an-xml-type-variable

但是当我尝试使用此示例数据时，当我想设置变量时它不起作用.我的 xml 文件还包含单引号，这就是我选择此示例的原因.

声明@myDoc xml声明 @ProdID intSET @myDoc = '<目录><book id="bk101"><author>Gambardella, Matthew</author><title>XML 开发人员指南</title><genre>计算机</genre><价格>44.95</价格><publish_date>2000-10-01</publish_date><description>深入了解创建应用程序</description><book id="bk102"><author>Ralls, Kim</author><title>午夜雨</title><genre>幻想</genre><价格>5.95</价格><publish_date>2000-12-16</publish_date><描述>一位前建筑师与企业僵尸作斗争，一个邪恶的女巫，和她自己成为女王的童年</description></目录>'

来自 https 的示例数据://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms762271(v=vs.85)

解决方案

您选择的示例数据包含引用 '.

<代码>...<title>XML 开发人员指南</title>...

这打破了文字字符串定义.SQL 引擎认为该字符串在 Developer 之后结束，并且不知道如何处理该字符串的其余部分，并试图告诉您 2 个错误...

<块引用>

Msg 102 Level 15 State 1 Line 6
's' 附近的语法不正确.

<块引用>

Msg 105 Level 15 State 1 Line 23
字符串 ' ' 后的非封闭引号.

您可以通过转义"解决此问题单引号和另一个引号如下:

<代码>...<title>XML 开发人员指南</title>...

小提琴

I am looking for a solution where I do not need to edit the file, or the copy pasted content manually. I am trying to use this method to query a xml document

DECLARE @myDoc xml  
DECLARE @ProdID int  
SET @myDoc = '<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features>  
  <Warranty>1 year parts and labor</Warranty>  
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>  
</Features>  
</ProductDescription>  
</Root>'  
  
SET @ProdID =  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )  
SELECT @ProdID

Example from https://docs.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver15#a-using-the-value-method-against-an-xml-type-variable

But when I try to use this sample data it will not work when I want to set the variable. My xml files also include single quotes that is why I choose this example.

DECLARE @myDoc xml  
DECLARE @ProdID int  
SET @myDoc = '<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies, 
      an evil sorceress, and her own childhood to become queen 
      of the world.</description>
   </book>
</catalog>'  

Sample data from https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms762271(v=vs.85)

解决方案

The sample data you have selected contains a quote '.

...
<title>XML Developer's Guide</title>
...

This breaks the literal string definition. The SQL engine thinks that the string ends after Developer and does not know what to do with the rest of that string and tries to tell you with 2 errors...

Msg 102 Level 15 State 1 Line 6
Incorrect syntax near 's'.

Msg 105 Level 15 State 1 Line 23
Unclosed quotation mark after the character string ' '.

You can fix this by "escaping" the single quote with another quote like so:

...
<title>XML Developer''s Guide</title>
...

Fiddle