問題描述

我正在尋找一種無需手動編輯文件或復制粘貼內容的解決方案.我正在嘗試使用此方法來查詢 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

這篇關于查詢 xml 數據 - 來自 Microsoft 的示例數據不起作用的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！