Xquery in SQL Server

Question

I am trying to parse my XML elements for the XML shown here. I need different elements from hierarchy.

I needs some attributes of all repeated elements of E1EDKA1 and E1EDK02.

If I set my XPath to

@XML.nodes('//*:ZINVOIC2/*:IDOC/*:E1EDKA1')

I am able to access elements of E1EDKA1 and E1EDK01. But I have multiple elements of E1EDK02.QUALF. How can I get all records of E1EDK02.QUALF in separate rows?

Code for this is

SELECT 
t.c.query('E1EDK01/BELNR').value('.','char(15)') as INV_NO,
 t.c.query('E1EDKA1/PARVW').value('.','char(10)') as CUST_NO,
 t.c.query('E1EDKA1/NAME1').value('.','char(50)') as CUST_NAME,
 t.c.query('E1EDK02/QUALF').value('.','char(50)') as QUALF
FROM @XML.nodes('//*:ZINVOIC2/*:IDOC') t(c);

How should I fix this. I have multiple such attributes and don't want to create different queries for each and then join back the result.

 <ZINVOIC2>
  <IDOC BEGIN="1">
    <EDI_DC40 SEGMENT="1">
      <TABNAM>EDI_DC40</TABNAM>
      <MANDT>100</MANDT>
      <DOCNUM>0000022222017973</DOCNUM>
       </EDI_DC40>
    <!--header general data information-->
    <E1EDK01 SEGMENT="1">
      <CURCY>GBP</CURCY>
      <HWAER>GBP</HWAER>
      <BELNR>9650019904</BELNR>
      <FKTYP>L</FKTYP>
      <Z1EDK01 SEGMENT="1">
        <TAXID>37-1652702</TAXID>
      </Z1EDK01>
      <Z1INCO SEGMENT="1">
        <INCO1>EXW</INCO1>
        <INCO1_TXT>Ex Works</INCO1_TXT>
      </Z1INCO>
      <ZINVREF SEGMENT="1">
        <ZNETDT>20220125</ZNETDT>
        <ZDSDT1>20220125</ZDSDT1>
        <DISCOUNTDUE>12427.39</DISCOUNTDUE>
        <SFAKN>N</SFAKN>
        <KALSM>YIS001</KALSM>
        <ZCONTACT_NUM>01542 832327</ZCONTACT_NUM>
        <FKART>YRIN</FKART>
        <KNUMV>0008498319</KNUMV>
        <VBTYP>M</VBTYP>
        <BEZEI>Ex Works</BEZEI>
        <KSCHL>ZMP6</KSCHL>
      </ZINVREF>
    </E1EDK01>
    <E1EDKA1 SEGMENT="1">
      <PARVW>RS</PARVW>
      <LIFNR>1111164896</LIFNR>
      <NAME1>XYZ Company</NAME1>
      <NAME2>XYZ Company Limited</NAME2>
      <STRAS>Sugarland str</STRAS>
      <ORT01>LONDON</ORT01>
      <PSTLZ>EC1A 4HD</PSTLZ>
      <LAND1>GB</LAND1>
      <SPRAS>E</SPRAS>
      <BNAME>PRODERP</BNAME>
      <PAORG>MK40</PAORG>
    </E1EDKA1>
    <E1EDKA1 SEGMENT="1">
      <PARVW>AG</PARVW>
      <PARTN>1111164896</PARTN>
      <NAME1>OILS LTD</NAME1>
      <STRAS>MARINE PLACE</STRAS>
      <ORT01>BUCKIE</ORT01>
      <PSTLZ>AB56 1UT</PSTLZ>
      <LAND1>GB</LAND1>
      <TELF1>01542 832327</TELF1>
      <TELFX>01542 833319</TELFX>
      <SPRAS>E</SPRAS>
      <REGIO>MR</REGIO>
      <SPRAS_ISO>EN</SPRAS_ISO>
    </E1EDKA1>
    <E1EDKA1 SEGMENT="1">
      <PARVW>RE</PARVW>
      <PARTN>1111164896</PARTN>
      <NAME1>OILS LTD</NAME1>
      <STRAS>MARINE PLACE</STRAS>
      <ORT01>BUCKIE</ORT01>
      <PSTLZ>AB56 1UT</PSTLZ>
      <LAND1>GB</LAND1>
      <TELF1>01542 832327</TELF1>
      <TELFX>01542 833319</TELFX>
      <SPRAS>E</SPRAS>
      <REGIO>MR</REGIO>
      <SPRAS_ISO>EN</SPRAS_ISO>
    </E1EDKA1>
    <E1EDKA1 SEGMENT="1">
      <PARVW>RG</PARVW>
      <PARTN>1111164896</PARTN>
      <NAME1>OILS LTD</NAME1>
      <STRAS>MARINE PLACE</STRAS>
      <ORT01>BUCKIE</ORT01>
      <PSTLZ>AB56 1UT</PSTLZ>
      <LAND1>GB</LAND1>
      <TELF1>01542 832327</TELF1>
      <TELFX>01542 833319</TELFX>
      <SPRAS>E</SPRAS>
      <REGIO>MR</REGIO>
      <SPRAS_ISO>EN</SPRAS_ISO>
    </E1EDKA1>
    <E1EDKA1 SEGMENT="1">
      <PARVW>BK</PARVW>
      <LIFNR>1040</LIFNR>
      <NAME1>XYZ Mktg Br</NAME1>
      <NAME2>ABC Ltd</NAME2>
      <STRAS>Sugarstreet</STRAS>
      <ORT01>LONDON</ORT01>
      <PSTLZ>EC1A 4HD</PSTLZ>
      <LAND1>GB</LAND1>
    </E1EDKA1>
    <!--header reference data-->
    <E1EDK02 SEGMENT="1">
      <QUALF>009</QUALF>
      <BELNR>9659999904</BELNR>
      <DATUM>20220101</DATUM>
    </E1EDK02>
    <E1EDK02 SEGMENT="1">
      <QUALF>002</QUALF>
      <BELNR>0201555555</BELNR>
      <DATUM>20220120</DATUM>
    </E1EDK02>
    <E1EDK02 SEGMENT="1">
      <QUALF>012</QUALF>
      <BELNR>0082222514</BELNR>
      <DATUM>20210423</DATUM>
    </E1EDK02>
    <E1EDK02 SEGMENT="1">
      <QUALF>017</QUALF>
      <BELNR>9650019904</BELNR>
    </E1EDK02>
    <E1EDK02 SEGMENT="1">
      <QUALF>087</QUALF>
      <BELNR>9650019904</BELNR>
    </E1EDK02>`enter code here`
    <!--date segment-->
    <E1EDK03 SEGMENT="1">
      <IDDAT>026</IDDAT>
      <DATUM>20220120</DATUM>
    </E1EDK03>
    <E1EDK03 SEGMENT="1">
      <IDDAT>001</IDDAT>
      <DATUM>20210423</DATUM>
    </E1EDK03>
    <E1EDK03 SEGMENT="1">
      <IDDAT>012</IDDAT>
      <DATUM>20220120</DATUM>
    </E1EDK03>
    <E1EDK03 SEGMENT="1">
      <IDDAT>011</IDDAT>
      <DATUM>20220120</DATUM>
    </E1EDK03>
    <E1EDK03 SEGMENT="1">
      <IDDAT>024</IDDAT>
      <DATUM>20220101</DATUM>
    </E1EDK03>
    <E1EDK03 SEGMENT="1">
      <IDDAT>028</IDDAT>
      <DATUM>20220115</DATUM>
    </E1EDK03>
    <E1EDK03 SEGMENT="1">
      <IDDAT>048</IDDAT>
      <DATUM>20220101</DATUM>
    </E1EDK03>
    <!--taxes-->
    <E1EDK04 SEGMENT="1">
      <MWSKZ>G1</MWSKZ>
      <MSATZ>20.000</MSATZ>
      <MWSBT>1394.86</MWSBT>
    </E1EDK04>
  </IDOC>
</ZINVOIC2>

Please post your query (and preferably input XML and desired output) as text, not images or fragments. As written it's virtually impossible for anyone to help out unless they first imagine what your input looks like and then manually re-type everything, which few people will be willing to do. If your input is too large, at least condense it to a minimal reproducible example. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 18, 2022 at 21:33
While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Feb 18, 2022 at 21:38
Bad habits to kick: using wildcard namespaces in Xquery, e.g.: *:ZINVOIC2, is not very performant and likely to yield unexpected results at some point. — AlwaysLearning
– AlwaysLearning, Commented Feb 18, 2022 at 23:08

AlwaysLearning · Accepted Answer · 2022-02-20 05:51:28Z

2

The E1EDK02/QUALF values determine the nature of the E1EDK02 contents, you can cross apply them against the IDOC element (Intermediate Document) like so...

declare @xml xml =
N'<INVOIC02 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="INVOIC02.xsd">
  <IDOC BEGIN="1">
    <EDI_DC40 SEGMENT="1" />
    <E1EDK02 SEGMENT="1">
      <QUALF>009</QUALF>
      <BELNR>239238232</BELNR>
      <DATUM>20190111</DATUM>
    </E1EDK02>
    <E1EDK02 SEGMENT="1">
      <QUALF>001</QUALF>
      <BELNR>592382323</BELNR>
      <DATUM>20190104</DATUM>
    </E1EDK02>
    <E1EDK02 SEGMENT="1">
      <QUALF>002</QUALF>
      <BELNR>292847342</BELNR>
      <DATUM>20190104</DATUM>
    </E1EDK02>
  </IDOC>
</INVOIC02>';

select
  InvoiceNumber.value('(QUALF/text())[1]', 'varchar(3)') as Invoice_QUALF,
  InvoiceNumber.value('(BELNR/text())[1]', 'varchar(35)') as Invoice_BELNR,
  InvoiceNumber.value('(DATUM/text())[1]', 'date') as Invoice_DATUM,
  InvoiceNumber.value('(UZEIT/text())[1]', 'time') as Invoice_UZEIT,

  CustomerPurchaseOrderNumber.value('(QUALF/text())[1]', 'varchar(3)') as PurchaseOrder_QUALF,
  CustomerPurchaseOrderNumber.value('(BELNR/text())[1]', 'varchar(35)') as PurchaseOrder_BELNR,
  CustomerPurchaseOrderNumber.value('(DATUM/text())[1]', 'date') as PurchaseOrder_DATUM,
  CustomerPurchaseOrderNumber.value('(UZEIT/text())[1]', 'time') as PurchaseOrder_UZEIT,

  SupplierOrderNumber.value('(QUALF/text())[1]', 'varchar(3)') as SupplierOrder_QUALF,
  SupplierOrderNumber.value('(BELNR/text())[1]', 'varchar(35)') as SupplierOrder_BELNR,
  SupplierOrderNumber.value('(DATUM/text())[1]', 'date') as SupplierOrder_DATUM,
  SupplierOrderNumber.value('(UZEIT/text())[1]', 'time') as SupplierOrder_UZEIT

from @xml.nodes('/INVOIC02/IDOC') as IDOC(IntermediateDocument)
cross apply IntermediateDocument.nodes('E1EDK02[QUALF/text()="009"]') E1EDK02_009(InvoiceNumber)
cross apply IntermediateDocument.nodes('E1EDK02[QUALF/text()="001"]') E1EDK02_001(CustomerPurchaseOrderNumber)
cross apply IntermediateDocument.nodes('E1EDK02[QUALF/text()="002"]') E1EDK02_002(SupplierOrderNumber);

Invoice_QUALF	Invoice_BELNR	Invoice_DATUM	Invoice_UZEIT	PurchaseOrder_QUALF	PurchaseOrder_BELNR	PurchaseOrder_DATUM	PurchaseOrder_UZEIT	SupplierOrder_QUALF	SupplierOrder_BELNR	SupplierOrder_DATUM	SupplierOrder_UZEIT
009	239238232	2019-01-11	null	001	592382323	2019-01-04	null	002	292847342	2019-01-04	null

Edit: after example ZINVOIC2 data was provided in the question...

The following query will produce the result set that you've asked for, but depending on your goals that result set may not be entirely useful since it is multiplying the iDoc Partner Profile Data against the iDoc Reference Data:

select 
  GeneralData.value('(BELNR/text())[1]', 'char(15)') as INV_NO,
  PartnerProfile.value('(PARVW/text())[1]', 'char(10)') as CUST_NO,
  PartnerProfile.value('(NAME1/text())[1]', 'char(50)') as CUST_NAME,
  ReferenceData.value('(QUALF/text())[1]', 'char(50)') as QUALF
from @XML.nodes('/ZINVOIC2/IDOC') IDOC(IntermediateDocument)
cross apply IntermediateDocument.nodes('E1EDK01') E1EDK01(GeneralData)
cross apply IntermediateDocument.nodes('E1EDKA1') E1EDKA1(PartnerProfile)
cross apply IntermediateDocument.nodes('E1EDK02') E1EDK02(ReferenceData)
order by row_number() over (order by ReferenceData, PartnerProfile);

INV_NO	CUST_NO	CUST_NAME	QUALF
9650019904	RS	XYZ Company	009
9650019904	AG	OILS LTD	009
9650019904	RE	OILS LTD	009
9650019904	RG	OILS LTD	009
9650019904	BK	XYZ Mktg Br	009
9650019904	RS	XYZ Company	002
9650019904	AG	OILS LTD	002
9650019904	RE	OILS LTD	002
9650019904	RG	OILS LTD	002
9650019904	BK	XYZ Mktg Br	002
9650019904	RS	XYZ Company	012
9650019904	AG	OILS LTD	012
9650019904	RE	OILS LTD	012
9650019904	RG	OILS LTD	012
9650019904	BK	XYZ Mktg Br	012
9650019904	RS	XYZ Company	017
9650019904	AG	OILS LTD	017
9650019904	RE	OILS LTD	017
9650019904	RG	OILS LTD	017
9650019904	BK	XYZ Mktg Br	017
9650019904	RS	XYZ Company	087
9650019904	AG	OILS LTD	087
9650019904	RE	OILS LTD	087
9650019904	RG	OILS LTD	087
9650019904	BK	XYZ Mktg Br	087

edited Feb 20, 2022 at 5:51

answered Feb 18, 2022 at 23:41

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Yitzhak Khabinsky Over a year ago

@AlwaysLearning, it is a mystery to me how it is possible to answer such question without a minimal reproducible example ##1-4. It known next to anything about input XML namespaces, same with the desired output.

AlwaysLearning Over a year ago

@YitzhakKhabinsky Unfortunately I've been exposed to numerous SAP integrations over the years.

Yitzhak Khabinsky Over a year ago

@AlwaysLearning, good deal. 👍Please up-vote my suggestion: feedback.azure.com/d365community/idea/…

AlwaysLearning Over a year ago

@YitzhakKhabinsky with pleasure! Thank you for taking the initiative.

avani jain Over a year ago

Still does not return values for me with above query.

|

Collectives™ on Stack Overflow

Xquery in SQL Server

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related