SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Doubt querying XML file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masta
Starting Member

2 Posts

Posted - 04/08/2014 :  07:21:07  Show Profile  Reply with Quote
Hi,

Having this XML file:

quote:

<?xml version="1.0"?>
<STOCK>
  <PRODUCTS>
    <PRODUCT>
      <ID>KM1063-06</ID>
      <QUANTITY>79597</QUANTITY>
    </PRODUCT>
    <PRODUCT>
      <ID>WD1100-03</ID>
      <QUANTITY>0</QUANTITY>
      <ARRIVAL>
        <QUANTITY>3968</QUANTITY>
        <DATE>20140418</DATE>
      </ARRIVAL>
    </PRODUCT>
  </PRODUCTS>
</STOCK>



I'm not sure how to query this XML file in order to get the "arrival date" and "arrival quantity" if they appear.

This query is wrong:

quote:

DECLARE @xml XML
SELECT @xml = x.y FROM OPENROWSET (BULK 'C:\files\stock.xml',SINGLE_BLOB) as x(y)

SELECT  
   x.y.value('ID[1]','varchar(15)'),
   x.y.value('QUANTITY[1]','int'),
   x.y.value('ARRIVAL/DATE[1]','int'),
   x.y.value('ARRIVAL/QUANTITY[1]','int'),
   'tr'
FROM   @xml.nodes('STOCK/PRODUCTS/PRODUCT') x(y)



Thanks in advance.

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/08/2014 :  09:03:44  Show Profile  Reply with Quote
The only thing I see wrong with it is that you need brackets like shown below. Or, are you looking for output in some other format? Can you have multiple arrival nodes within a single product node?
SELECT  
   x.y.value('ID[1]','varchar(15)'),
   x.y.value('QUANTITY[1]','int'),
   x.y.value('(ARRIVAL/DATE)[1]','int'),
   x.y.value('(ARRIVAL/QUANTITY)[1]','int'),
   'tr'
FROM   @xml.nodes('STOCK/PRODUCTS/PRODUCT') x(y)
Go to Top of Page

masta
Starting Member

2 Posts

Posted - 04/08/2014 :  09:58:22  Show Profile  Reply with Quote
Works perfect now.

Thank you James.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000