CODEWITHSUNDEEP
sqlsql-serverxml
Lasse Edsvik
Lasse Edsvik

Reputation: 9298

Select multiple xml nodes using crossapply

I'm currently trying to select multiple nodes for a xml variable that get passed into a stored procedure. But I only get one node from it's xml. How can I list all of the item names and string values?

Test code:

DECLARE @T TABLE (AllXml ntext)

INSERT @T VALUES('<error>
        <item name="item 1">
        <value string="string 1" />
        </item>
        <item name="item 2">
        <value string="string 2" />
        </item>
        <item name="item 3">
        <value string="string 3" />
        </item>
        </error>')

SELECT 
    CAST(AllXml as xml).value('(/error/item/@name)[1]', 'varchar(100)' ),
    CAST(AllXml as xml).value('(/error/item/value/@string)[1]', 'varchar(max)' )
        FROM @T

Desired result:

Item 1     string 1
Item 2     string 2
Item 3     string 3

Upvotes: 0

Views: 71

Answers (1)

Mahesh
Mahesh

Reputation: 8892

You can achieve it using the CROSS Apply. And Sub-Select.

SELECT 
    m.c.value('(@name)[1]', 'varchar(100)') AS Name,
    m.c.value('(value/@string)[1]', 'varchar(max)') AS Value
FROM 
     (
        SELECT CAST(AllXml as xml) AllXml 
        FROM @T
     ) AS data
    CROSS APPLY AllXml.nodes('/error/item') as m(c)

Or you can use the one more CROSS APPLY as below,

   SELECT 
        m.c.value('(@name)[1]', 'varchar(100)') AS Name,
        m.c.value('(value/@string)[1]', 'varchar(max)') AS Value
    FROM @T
        CROSS APPLY (SELECT CAST(AllXml AS XML)) as D(D)
        CROSS APPLY D.D.nodes('/error/item') as m(c)

Upvotes: 1

Related Questions