Reading XML repeated tags in sql server -

 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 reading data using following query

declare @innerxml xml;  select @innerxml = t.c.query('<result>{/result/cash}</result>')   @mainxml.nodes('result') t(c)  select   result.claim.value('(./@number)[1]','varchar(max)') c1, result.claim.value('(./@amt)[1]','varchar(max)') c2, result.claim.value('(./@status)[1]','varchar(max)') c3    @innerxml.nodes('/result/cash/account') result(claim) 

i want read xml , store in db below.

c1   c2     c3 ---------------- 10   11.00  closed 10   12.00  closed 20   21.00  closed 20   22.00  closed 

but query returns null in c1 column please me here. in advance

you should not use parent axis in xml queries in sql server. query plan created o(n²). every node in xml nodes in xml checked.

first shred on result/cash , shred on account in cross apply.

select c.x.value('@number', 'varchar(max)') c1,        a.x.value('@amt', 'varchar(max)') c2,        a.x.value('@status', 'varchar(max)') c3 @mainxml.nodes('result/cash') c(x)   cross apply c.x.nodes('account') a(x) 

_{don't see point of creating second xml variable. use @mainxml directly.}