Search code examples
sqlsql-serverxmlxpathsqlxml

Processing XML Hierarchy with MS SQL

How would I specify the following XML Hierarchy into readable columns in Microsoft SQL?

<transaction id=1>
    <item id=1>
        <price>1</price>
    </item>
    <item id=2>
        <price>1</price>
    </item>
</transaction>
<transaction>
    <item id=1>
        <price>1</price>
    </item>
</transaction>

for instance

select
    x.i.value('(????)','Varchar(max)') [TransId]
    x.i.value('(????)','Varchar(max)') [ItemId]
    x.i.value('(????)','Varchar(max)') [PriceId]
from @xml.nodes('/transaction') x(i)

Thanks in advance.

Solution

  • Actually usually it's faster to shred XML from parent to child using apply, like this:

    select
    t.c.value('@id','int') as TransId,
    i.c.value('@id','int') as ItemId,
    i.c.value('(price/text())[1]', 'int') as PriceId
from @xml.nodes('transaction') as t(c)
    outer apply t.c.nodes('item') as i(c)
order by TransId, ItemID

    sql fiddle demo