Processing XML Hierarchy with MS SQL

Question

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.

bummi · Accepted Answer · 2013-09-23 23:30:05Z

1

Attribute values must always appear in quotation marks in XML. Not sure about the desired output. An example would be:

declare @xml xml
Select @xml=
'<transaction id="1">
    <item id="1">
        <price>1</price>
    </item>
    <item id="2">
        <price>2</price>
    </item>
</transaction>
<transaction>
    <item id="1">
        <price>3</price>
    </item>
</transaction>'

SELECT
    y.value('../@id','int') as TransactionID,
    y.value('@id','int') as ItemID,
    y.value('(./price/text())[1]', 'Varchar(max)') as Price
FROM @xml.nodes('/transaction/item') as x(y)
order by TransactionID,ItemID

with the output:

NULL    1   3
1       1   1
1       2   2

answered Sep 23, 2013 at 23:30

bummi

27.4k14 gold badges67 silver badges105 bronze badges

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

1 Comment

user2808765 Over a year ago

OK, so I reviewed some tutorial guides and took in your suggestion. Im coming back with results, however its taking 20mins to shred just 1 XML file.If I remove the parent look up(../@id) then it returns results instantly. The Problem is the parent ID isn't in the child elements as an attribute, so I MUST pull this back. Any suggestions on how I could tune the performance specifically for this bottleneck. Or how to understand why this is a bottleneck. I've tried doing a XSD setup into a SQL schema and then creating primary and secondary XML indexes and got the same speed results.

roman · Accepted Answer · 2013-09-25 20:04:37Z

0

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

edited Sep 25, 2013 at 20:04

answered Sep 25, 2013 at 19:07

roman

118k30 gold badges205 silver badges209 bronze badges

1 Comment

user2808765 Over a year ago

Thanks a million man, this is the performance issue I was having. I was trying to build the hierarchy backwards. went from 20 min run time to 2 seconds. Thanks again.

Collectives™ on Stack Overflow

Processing XML Hierarchy with MS SQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related