Reading duplicate XML 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 am reading data using the 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 xml and save to DB as shown below.
C1 C2 C3
----------------
10 11.00 Closed
10 12.00 Closed
20 21.00 Closed
20 22.00 Closed
but my query only returns NULL in column C1 Please help me here. thanks in advance
source to share
You should not use the parent axis in XML queries in SQL Server. The generated query plan will be O (n 2 ). For every node in XML, all nodes in XML are validated.
First it will be covered result/cash
and then cropped account
into a cross.
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)
<sub> I see no point in creating a second XML variable. Use @MainXML
directly. Sub>
source to share
@number
is an attribute <cash>
, but your context node is <account>
. To access the attribute, you need to traverse the XML tree one level to get to the <cash>
node. You can do ..
to get to the parent of the current node in the 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)
source to share