Sunday, March 1, 2015

How to get table result from XML

Hi I am new to XML, I was playing with node, but wasn't successfull


I have XML fragment



DECLARE @XML xml =

'
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>A1</IDTNR>
<KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>E1</IDTNR>
</E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>A2</IDTNR>
<KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>E2</IDTNR>
</E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>A3</IDTNR>
<KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>E3</IDTNR>
</E1EDP19>
</E1EDP01>'

and I need to create query, which list all products and his two codes, result should be like this
























ProductCode1ProductCode2
A1E1
A2E2
A3E3

It is visible from XML fragment, how those values are organised in segments......


I tried to use



select PC1.query('./text()')

AS PC1,

PC2.query('./text()')

AS PC2

from @XML.nodes('/E1EDP01/E1EDP19[1]/IDTNR') as PC1(PC1)

CROSS APPLY @XML.nodes('/E1EDP01/E1EDP19[2]/IDTNR') as PC2(PC2)

But it gets all combinations of ProductCode1 and PRoductCode2, somtehing like cross join and result has 9 rows...


Could you help please


No comments:

Post a Comment