My xml is something like this : <XMLDATA> <DETAILS> <PARAM1>1</PARAM1> <SPECS> <PARAM2>1</PARAM2> </SPECS> <SPECS> <PARAM2>2</PARAM2> </SPECS> </DETAILS> <DETAILS> <PARAM1>2</PARAM1> </DETAILS> <DETAILS> <PARAM1>3</PARAM1> </DETAILS> </XMLDATA>
I am looking for a table result like this :
PARAM1 PARAM2
-------- --------
1 1
1 2
2 NULL
3 NULL
I try this :
SELECT
tab.col.value('PARAM[1]','INT')AS PARAM1,
d.col.value('PARAM2[1]','INT') AS PARAM2
FROM @PRODUCTS_XML.nodes('//DETAILS') tab(col)
CROSS APPLY tab.col.nodes('SPECS') d(col)
But it just return the 1st row. Thanks.
No comments:
Post a Comment