CODEWITHSUNDEEP
sql-serverxmlt-sql
xhr489
xhr489

Reputation: 2309

Query xml data - sample data from Microsoft not working

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://learn.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://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms762271(v=vs.85)

Upvotes: 0

Views: 99

Answers (1)

Sander
Sander

Reputation: 4042

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

Upvotes: 1

Related Questions