Get XML node values using SQL

Question

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?

Your XML is invalid - the <title> nodes should each have an </title> endnode - not a </text> .... — marc_s
– marc_s, Commented Jan 14, 2016 at 21:58

marc_s · Accepted Answer · 2016-01-14 21:57:44Z

1

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)

answered Jan 14, 2016 at 21:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user721126 Over a year ago

Thank you!! One more quick question, I have another column called 'Store #' that is included in the query as the first column. Sorry I forgot to add that. How can I have the results grouped by that column?

marc_s Over a year ago

@user721126: a grouping only makes sense if you have columns in your select list that have aggregates applied to them - COUNT, MIN/MAX, AVG etc. Maybe you just mean order by (sorting)? Sure, that's not problem - just add ORDER BY StoreNumber at the end

Collectives™ on Stack Overflow

Get XML node values using SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related