Reading XML repeated tags in sql server

Question

 Declare @MainXml XML = 
 '<?xml version="1.0" encoding="utf-8"?>
 <result>
 <cash number="10">
 <account amt="11.00" status="Closed"/>
 <account amt="12.00" status="Closed"/>                       
 </cash>        
 <cash number="20">
 <account amt="21.00" status="Closed"/>
 <account amt="22.00" status="Closed"/>                       
 </cash>        
 </result>'

I am reading data using following query

Declare @Innerxml xml; 
SELECT @Innerxml = T.c.query('<result>{/result/cash}</result>')
FROM   @MainXml.nodes('result') T(c)

SELECT  
Result.Claim.value('(./@number)[1]','varchar(max)') as C1,
Result.Claim.value('(./@amt)[1]','varchar(max)') as C2,
Result.Claim.value('(./@status)[1]','varchar(max)') as C3   
From @Innerxml.nodes('/result/cash/account') Result(Claim)

I want to read the xml and store in DB as below.

C1   C2     C3
----------------
10   11.00  Closed
10   12.00  Closed
20   21.00  Closed
20   22.00  Closed

but my query returns only NULL in C1 column Please help me here. Thanks in advance

Mikael Eriksson · Accepted Answer · 2015-04-23 06:30:06Z

2

You should not use the parent axis in XML queries in SQL Server. The query plan created will be O(n²). For every node in the XML all nodes in the XML is checked.

First shred on result/cash and then shred on account in a cross apply.

select C.X.value('@number', 'varchar(max)') as C1,
       A.X.value('@amt', 'varchar(max)') as C2,
       A.X.value('@status', 'varchar(max)') as C3
from @MainXml.nodes('result/cash') as C(X)
  cross apply C.X.nodes('account') as A(X)

_{I don't see the point of creating a second XML variable. Use @MainXML directly.}

answered Apr 23, 2015 at 6:30

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

7 Comments

roman Over a year ago

While it sounds logical, I'm not sure this is always true. I reckon I've seen queries with .. sometimes performed better than ones with several applies like this, but I don't have any exact example at the moment. That said, it would be interesting to test it

roman Over a year ago

Also I disagree that plan will be O(n^2), it should be linear, because every node has only one parent

Mikael Eriksson Over a year ago

@RomanPekar have a look at stackoverflow.com/questions/24196516/…

roman Over a year ago

thanks, nice answer! As I said, it makes perfect sense that it's better to shred xml like you did here and this is always my first try, I just rememeber that I've seen the situations where parent node works better, but again, no example for now.

Mikael Eriksson Over a year ago

@RomanPekar perhaps you had a XML index in place? The plan is different if you do.

|

har07 · Accepted Answer · 2015-04-23 04:00:52Z

2

@number is attribute of <cash> but your context node is <account>. You need to go up the XML tree one level to get to <cash> node before accessing the attribute. You can do .. to get to parent of current node in xpath :

SELECT  
Result.Claim.value('(../@number)[1]','varchar(max)') as C1,
Result.Claim.value('(./@amt)[1]','varchar(max)') as C2,
Result.Claim.value('(./@status)[1]','varchar(max)') as C3   
From @Innerxml.nodes('/result/cash/account') Result(Claim)

answered Apr 23, 2015 at 4:00

har07

89.5k12 gold badges87 silver badges143 bronze badges

Collectives™ on Stack Overflow

Reading XML repeated tags in sql server

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related