Doubt querying XML file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Doubt querying XML file

Author

Topic

masta
Starting Member

2 Posts

Posted - 2014-04-08 : 07:21:07

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-08 : 09:03:44

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)

masta
Starting Member

2 Posts

Posted - 2014-04-08 : 09:58:22

Works perfect now.

Thank you James.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources