Tuesday, 17 February 2015

Read multi level xml with null value if xml node not exists



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