CODEWITHSUNDEEP
sql-serverxmlt-sql
Juan Felix
Juan Felix

Reputation: 9

Convert XML column to row in SQL Server

I have table combine with XML format for store multi value data. sample like this

ID NAME AMOUNT
1 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>1000</m><m>100</m><m>10</m></multi>
2 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>2000</m><m>200</m><m>20</m></multi>
3 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>3000</m><m>300</m><m>30</m></multi>
4 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>4000</m><m>400</m><m>40</m></multi>
5 <multi><m>BALANCE</m><m>INTEREST</m><m>FEE</m></multi> <multi><m>5000</m><m>500</m><m>50</m></multi>

I need to convert this XML to row, repeat by ID

ID NAME AMOUNT
1 BALANCE 1000
1 INTEREST 100
1 FEE 10
2 BALANCE 2000
2 INTEREST 200
2 FEE 20
3 BALANCE 3000
3 INTEREST 300
3 FEE 30
4 BALANCE 4000
4 INTEREST 400
4 FEE 40
5 BALANCE 5000
5 INTEREST 500
5 FEE 50

How to achieve this query in SQL Server?

I have tried using this code:

SELECT  
    p.ID, 
    NAME = Name.value('.', 'varchar(MAX)'), 
    AMOUNT = Amount.value('.', 'varchar(MAX)')
FROM 
    PRODUCTS p 
CROSS APPLY 
    NAME.nodes('/multi/m') AS tag (Name)
CROSS APPLY 
    AMOUNT.nodes('/multi/m') AS tag2 (Amount)

The result is not what I expected because NAME will repeat for every AMOUNT

Upvotes: 0

Views: 505

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8829

It seems like you're wanting to select matching pairs of m XML nodes between the NAME and AMOUNT columns. You could do this by making use of the sql:column() XQuery function in conjunction with some index values to select them by their child index under, e.g.:

SELECT
  p.ID,
  NAME = tag.Name.value('.', 'varchar(MAX)') ,
  AMOUNT = tag2.Amount.value('.', 'varchar(MAX)')
FROM PRODUCTS p
cross apply (values (1), (2), (3) ) L (LinkingIndex)
cross apply NAME.nodes('(/multi/m)[sql:column("LinkingIndex")]') AS tag ( Name )
cross apply AMOUNT.nodes('(/multi/m)[sql:column("LinkingIndex")]') AS tag2 ( Amount );

Which yields the result...

ID NAME AMOUNT
1 BALANCE 1000
1 INTEREST 100
1 FEE 10
2 BALANCE 2000
2 INTEREST 200
2 FEE 20
3 BALANCE 3000
3 INTEREST 300
3 FEE 30
4 BALANCE 4000
4 INTEREST 400
4 FEE 40
5 BALANCE 5000
5 INTEREST 500
5 FEE 50

Upvotes: 1

Related Questions