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
ProductCode1 | ProductCode2 |
A1 | E1 |
A2 | E2 |
A3 | E3 |
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