CODEWITHSUNDEEP
xmlt-sqlsql-server-2008-r2xquery
Jeremy
Jeremy

Reputation: 46322

How to query xml across multiple rows in SQLServer 2008R2

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

Upvotes: 3

Views: 3280

Answers (1)

Pero P.
Pero P.

Reputation: 26982

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.

Upvotes: 6

Related Questions