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

Answer 1

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.}

Answer 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)