Convert XML column to row in SQL Server

Question

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

Without seeing the actual XML how can we advise? For example, where does multi/m come from, and why would you shred the same nodes twice? — Charlieface
– Charlieface, Commented May 3, 2021 at 12:04

AlwaysLearning · Accepted Answer · 2021-05-03 11:20:42Z

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

Collectives™ on Stack Overflow

Convert XML column to row in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related