CODEWITHSUNDEEP
sqlxmlsql-server-2014
user721126
user721126

Reputation: 143

Get XML node values using SQL

I'm using SQL Server 2014 and I need help with constructing a query that will give me all of the values for a particular XML node. I have a table that has information stored in it in xml. The xml data is like below:

<category>
   <text>Laser Printers</text>
   <title id="1">
      <text>HP OfficeJet Pro 8610</text>
   </text>
   <title id="2">
      <text>HP OfficeJet Pro 8700</text>
   </text>
   <title id="3">
      <text>Canon PIXMA MX 922</text>
   </text>       
</category>

I'm only able to get the value from the first node for each row in the table using this query.

SELECT it.contents.value('(category/title/text)[1]', 'NVARCHAR(255)') as Printer
FROM inventory i
INNER JOIN store_products p ON i.inventoryId = p.inventoryId
INNER JOIN items it ON p.itemId = it.itemId

So my result is:

Printer
HP OfficeJet Pro 8610

What I need is the following:

Printer
HP OfficeJet Pro 8610, HP OfficeJet Pro 8700, Canon PIXMA MX 922

Is this even possible?

Upvotes: 0

Views: 3012

Answers (1)

marc_s
marc_s

Reputation: 754258

You need to use CROSS APPLY and the .nodes() XQuery function - something like this:

SELECT 
    XC.value('(text)[1]', 'varchar(100)') AS Printer
FROM 
    inventory i
INNER JOIN 
    store_products p ON i.inventoryId = p.inventoryId
INNER JOIN 
    items it ON p.itemId = it.itemId
CROSS APPLY
    it.contents.nodes('/category/title') AS XT(XC)

Upvotes: 1

Related Questions