How to query xml across multiple rows in SQLServer 2008R2

Question

In a table, I have multiple rows, each containing xml based on common schema. An example of the XML might be:

<Items>
    <Item>Item 1</Item>
    <Item>Item 2</Item>
</Items>

If I have multiple rows in a table, all containing similar xml, is it possible to write a query that returns all values in the Item node for all rows, in a single resultset? We're using SQL Server 2008 R2

Is the column defined as XML datatype?

Pero P.
– Pero P.

2012-09-12 00:01:22 +00:00
Commented Sep 12, 2012 at 0:01 — Pero P.
– Pero P., Commented Sep 12, 2012 at 0:01

Pero P. · Accepted Answer · 2012-09-12 00:23:22Z

6

If your xml is defined in an XML column ..

DECLARE @Items AS TABLE 
(
    ItemXml XML
)

-- test data with a couple rows of xml
INSERT INTO @Items(ItemXml)
VALUES ('<Items><Item>Item 1</Item><Item>Item 2</Item></Items>')
      ,('<Items><Item>Item 3</Item><Item>Item 4</Item></Items>')

-- the query
SELECT t.i.value('.','VARCHAR(8000)') AS Item
FROM @Items CROSS APPLY ItemXml.nodes('/Items/Item') t(i)

Will give you

Item
------
Item 1
Item 2
Item 3
Item 4

The key here is nodes() which

is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.

edited Sep 12, 2012 at 0:23

answered Sep 12, 2012 at 0:03

Pero P.

27.1k9 gold badges65 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

How to query xml across multiple rows in SQLServer 2008R2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related