CODEWITHSUNDEEP
sqlsql-servert-sqlxml-parsing
Travis
Travis

Reputation: 705

How can I select from an xml type in a table?

I can get the values I need as follows:

DECLARE @xml xml;
SELECT TOP 1  @xml = InaSuQtyXml 
FROM csn_inventory..tblInventoryCache_st4 
WHERE InaDateUpd > '2014-11-01' and InaQty > 0;

SELECT b.value('@SuID', 'int') SuID, b.value('@Quantity', 'int') Quantity
FROM @xml.nodes('/Inventory/Supplier') as a(b);`

And it returns:

SuID | Quantity
-----+---------
 275 | 333
7417 | -1
15203| 48

However, when I try to do the same and pull more information from the table as follows, I get an error message that says "nodes" is not a valid function, property, or field. 

SELECT
TOP 1
    InaPrSKU,
    InaDateUpd,
    (InaSuQtyXml).nodes('/Inventory/Supplier').value('@SuID', 'int') SuID,
    (InaSuQtyXml).nodes('/Inventory/Supplier').value('@Quantity', 'int') Quantity
FROM csn_inventory..tblInventoryCache_st4 
WHERE InaDateUpd > '2014-11-01' and InaQty > 0;

I was hoping that query would result in the following, but it doesn't seem to be working. Is there any way to get the following?

InaPruSKU | InaDateUpd | SuID | Quantity
----------+------------+------+---------
  AA1433  | 2014-11-10 | 275  | 333
  AA1433  | 2014-11-10 | 7417 | -1
  AA1433  | 2014-11-10 | 15203| 48

Upvotes: 0

Views: 40

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

You need to have the cross apply to get the value from node function in the select. Try changing your query like this.

SELECT TOP 1 InaPrSKU,
             InaDateUpd,
             SuID = Container.value('(SuID)[1]', 'INT'),
             Quantity = Container.value('(Quantity)[1]', 'INT'),
FROM   csn_inventory..tblInventoryCache_st4
       CROSS apply InaSuQtyXml.nodes('/Root/Inventory/Supplier') AS T1 (Container)
WHERE  InaDateUpd > '2014-11-01'
       AND InaQty > 0;

Example :

CREATE TABLE T
  (c3 XML)

INSERT INTO t
            (c3)
VALUES     ('<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>')

SELECT Warranty = Container.value('(Warranty)[1]', 'varchar(50)'),
       Maintenance = Container.value('(Maintenance)[1]', 'varchar(100)')
FROM   T
       CROSS apply c3.nodes('/Root/ProductDescription/Features') AS T1 (Container)

Upvotes: 1

Related Questions