vignesh vignesh - 6 months ago 24
SQL Question

xml to sql tables

Need to convert xml into sql table.

Sample XML

<?xml version="1.0"?> <ConsumerTransactionList> <ConsumerTransaction>
<SourceSystem>
<SourceTimestamp>2016-03-02T16:10:33+08:00</SourceTimestamp>
<MarketCode>CHN</MarketCode>
</SourceSystem>
<TransactionHeader>
<PersonnelCode>4215</PersonnelCode>
<TransactionConsumer>
<ConsumerId>15859625</ConsumerId>
</TransactionConsumer>
</TransactionHeader>
<TransactionDetailList>
<TransactionDetail>
<LineNum>1</LineNum>
<TransactionTypeCode>sle</TransactionTypeCode>
</TransactionDetail>
<TransactionDetail>
<LineNum>2</LineNum>
<TransactionTypeCode>rdm</TransactionTypeCode>
</TransactionDetail>
</TransactionDetailList> </ConsumerTransaction> <ConsumerTransaction>
<SourceSystem>
<SourceTimestamp>2016-03-02T16:12:27+08:00</SourceTimestamp>
<MarketCode>KMU</MarketCode>
</SourceSystem>
<TransactionHeader>
<PersonnelCode>4152</PersonnelCode>
<TransactionConsumer>
<ConsumerId>48578589</ConsumerId>
</TransactionConsumer>
</TransactionHeader>
<TransactionDetailList>
<TransactionDetail>
<LineNum>1</LineNum>
<TransactionTypeCode>sle</TransactionTypeCode>
</TransactionDetail>
<TransactionDetail>
<LineNum>10</LineNum>
<TransactionTypeCode>rdm</TransactionTypeCode>
</TransactionDetail>
<TransactionDetail>
<LineNum>11</LineNum>
<TransactionTypeCode>rdm</TransactionTypeCode>
</TransactionDetail>
</TransactionDetailList> </ConsumerTransaction> </ConsumerTransactionList>


Expected output:
Expected output :

This is what I've tried so far:



This query generates permutation combination for the xml. Query need to populate a result set for each tag. But my query mingles all tags.Here only Im struggling.

SELECT
x.item.value('SourceTimestamp[1]','varchar(100)'), x.item.value('MarketCode[1]','varchar(100)'),
y.item.value('PersonnelCode[1]','varchar(100)')
,z.item.value('ConsumerId[1]','varchar(100)'),
x1.item.value('LineNum[1]','varchar(100)'), x1.item.value('TransactionTypeCode[1]','varchar(100)')

FROM
@x.nodes('//ConsumerTransaction/SourceSystem') AS x(item)
cross join @x.nodes('//ConsumerTransaction/TransactionHeader') AS y(item)
cross join @x.nodes('//ConsumerTransaction/TransactionHeader/TransactionConsumer') AS z(item)
cross apply @x.nodes('//ConsumerTransaction/TransactionDetailList/TransactionDetail') as x1(item)

Answer

Assuming your XML is in a variable named @test you might try this:

SELECT CT.value('(SourceSystem/SourceTimestamp)[1]','datetime') AS SourceTimeStamp 
      ,CT.value('(SourceSystem/MarketCode)[1]','varchar(max)') AS MarketCode
      ,CT.value('(TransactionHeader/PersonnelCode)[1]','int') AS PersonnelCode
      ,CT.value('(TransactionHeader/TransactionConsumer/ConsumerId)[1]','int') AS ConsumerId
      ,TD.value('LineNum[1]','int') AS LineNum
      ,TD.value('TransactionTypeCode[1]','varchar(max)') AS TransactionTypeCode
FROM @test.nodes('/ConsumerTransactionList/ConsumerTransaction') AS A(CT)
CROSS APPLY CT.nodes('TransactionDetailList/TransactionDetail') AS B(TD)