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
You should not use the parent axis in XML queries in SQL Server. The query plan created will be O(n2). 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.