0

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)

Dale K
  • 25,246
  • 15
  • 42
  • 71
xhr489
  • 1,957
  • 13
  • 39

1 Answers1

1

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

Sander
  • 3,942
  • 2
  • 17
  • 22
  • But I cannot do this to my real files i.e. change them manually. – xhr489 Sep 20 '20 at 10:31
  • I can search and replace. But there must be another solution – xhr489 Sep 20 '20 at 10:35
  • Depends on how you populate your XML variable with your "real files". Please create a new question that shows how you load your real files. – Sander Sep 20 '20 at 10:36
  • For now I just copy-paste the content in SSMS. When I have found a solution for this I will figure out how to proceed. – xhr489 Sep 20 '20 at 10:38
  • My files are many thousand lines, you say the solution is to find all the single quotes and escape them? I could of course do a search and replace... – xhr489 Sep 20 '20 at 10:40
  • 1
    I was expecting you to no copy-paste them... Have a look a [this](https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/) to import an entire file into one column or [this](https://www.mssqltips.com/sqlservertip/5707/simple-way-to-import-xml-data-into-sql-server-with-tsql/) to import the XML values in separate columns. [Another example](https://stackoverflow.com/questions/16838627/import-xml-into-sql-server) of that last scenario. – Sander Sep 20 '20 at 10:42
  • I cannot use bulk operation I have tried it says I do not have permissions... At least when I try to import xml... I will try to import the whole thing in one column...Thanks – xhr489 Sep 20 '20 at 10:43
  • I tried the first link it says You do not have permission to use the bulk load statement. Strange.. I can drop the database if I want but I cannot read a file... – xhr489 Sep 20 '20 at 10:49
  • 1
    The lack of permissions was (and currently still is) not part of your original question. This changes the question completely. With this new information I suggest you contact your database administrator to grant you the required privileges. – Sander Sep 20 '20 at 10:50
  • So with the copy-paste method in SSMS the only solution is to escape the single quotes? – xhr489 Sep 20 '20 at 10:53
  • As far as I can tell that would indeed be the case with your current approach. In order to validate this, please create a **new question** that shows how you attempted the bulk load of the XML document(s). Copy-paste the exact error message(s) you received. – Sander Sep 20 '20 at 11:00