Data from XML to MSSQL using CROSS APPLY

Question

I'm having trouble extracting some data from a XML file to MSSQL DB using CROSS APPLY, here is what I have:

Note: I did read about how to post here but pardon me if I'm still wrong.

XML File:

<?xml version="1.0" encoding="UTF-8" ?>
<ZMPROD01>
   <IDOC BEGIN="1">
      <EDI_DC40 SEGMENT="1">
         <TABNAM>EDI_DC40</TABNAM>
         <DOCNUM>0000003899888135</DOCNUM>
         <CREDAT>20220201</CREDAT>
         <CRETIM>152041</CRETIM>
      </EDI_DC40>
      <ZPROD SEGMENT="1">
         <WERKS>8285</WERKS>
         <LGNUM>0</LGNUM>
         <AUFNR>000915229446</AUFNR>
         <LINENO>RM01PL01</LINENO>
         <CHARG>0006186588</CHARG>
         <START1>20220202</START1>
         <START2>211609</START2>
         <QTY>4166.000</QTY>
         <END1>20220202</END1>
         <END2>240000</END2>
         <MAKTX>579 FUS5 75ML ULTRA SENST GEL</MAKTX>
         <PLN_ORDER>6963701111</PLN_ORDER>
         <Z1PRODI SEGMENT="1">
            <POSNR>000010</POSNR>
            <MATNR>000000000098920665</MATNR>
         </Z1PRODI>
         <Z1PRODI SEGMENT="1">
            <POSNR>000040</POSNR>
            <HRKFT>V010</HRKFT>
         </Z1PRODI>
         <Z1PRODI SEGMENT="1">
            <POSNR>000050</POSNR>
            <MATNR>000000000099396964</MATNR>
         </Z1PRODI>
      </ZPROD>
   </IDOC>
</ZMPROD01>

My SQL query:

INSERT INTO XMLTESTTABLE(PONo, ASP, LOTNo, EntryDate, StartDate, EndDate, GAS, PlannedQty, LineNum, SAPDesc, StartTime, EndTime)
SELECT
   MY_XML.ZPROD.query('AUFNR').value('.', 'VARCHAR(9)'),
   MY_XML.ZPROD.query('CHARG').value('.', 'VARCHAR(8)'),
   MY_XML.ZPROD.query('PLN_ORDER').value('.', 'VARCHAR(10)'),
   MY_XML.ZPROD.query('START1').value('.', 'date'),
   MY_XML.ZPROD.query('START1').value('.', 'date'),
   MY_XML.ZPROD.query('END1').value('.', 'date'),
   MY_XML.ZPROD.query('CHARG').value('.', 'VARCHAR(8)'),
   MY_XML.ZPROD.query('QTY').value('.', 'VARCHAR(9)'),
   MY_XML.ZPROD.query('LINENO').value('.', 'VARCHAR(1)'),
   MY_XML.ZPROD.query('MAKTX').value('.', 'VARCHAR(9)'),
   MY_XML.ZPROD.query('START2').value('.', 'time'),
   MY_XML.ZPROD.query('END2').value('.', 'time')
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\Users\PC_user\Documents\Idoc3899888135.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('ZMPROD01/ZPROD') AS MY_XML (ZPROD);

The result I get is (0 rows affected). I tried replacing ZMPROD01/ZPROD several times but different errors appeared, at some point complained about being unable to convert to date datatype.

On a separate note how, can I get data from EDI_DC40 as well? Not sure how the CROSS APPLY would look like to look for different places on the document.

Your help is appreciated.

Thanks.

Answer 1

Please try the following solution.

Notable points:

• I adjusted the XPath expression in the .nodes() method.
• No need to use the .query() method.
• text() is added for performance reasons.
• Last two data elements converted into TIME data type.
• As it was already mentioned, the <END2>240000</END2> is not a legit value for the TIME data type.

SQL

--INSERT INTO XMLTESTTABLE(PONo, ASP, LOTNo, EntryDate, StartDate, EndDate, GAS, PlannedQty, LineNum, SAPDesc, StartTime, EndTime)
SELECT ZPROD.value('(AUFNR/text())[1]', 'VARCHAR(9)')
    , ZPROD.value('(CHARG/text())[1]', 'VARCHAR(8)')
    , ZPROD.value('(PLN_ORDER/text())[1]', 'VARCHAR(10)')
    , ZPROD.value('(START1/text())[1]', 'date')
    , ZPROD.value('(START1/text())[1]', 'date')
    , ZPROD.value('(END1/text())[1]', 'date')
    , ZPROD.value('(CHARG/text())[1]', 'VARCHAR(8)')
    , ZPROD.value('(QTY/text())[1]', 'VARCHAR(9)')
    , ZPROD.value('(LINENO/text())[1]', 'VARCHAR(1)')
    , ZPROD.value('(MAKTX/text())[1]', 'VARCHAR(9)')
    , TRY_CAST(STUFF(STUFF(ZPROD.value('(START2/text())[1]', 'CHAR(6)'),3,0,':'),6,0,':') AS TIME)
    , TRY_CAST(STUFF(STUFF(ZPROD.value('(END2/text())[1]', 'CHAR(6)'),3,0,':'),6,0,':') AS TIME)
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'e:\Temp\Idoc3899888135.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('/ZMPROD01/IDOC/ZPROD') AS MY_XML (ZPROD);

Answer 2

Your path seems wrong - what about:

CROSS APPLY MY_XML.nodes('ZMPROD01/IDOC/ZPROD') AS MY_XML (ZPROD);

You seem to have forgotten the IDOC

And the data type is because you cannot convert the times to the data type time. Try starting with the select where everything is converted to varchar, then start changing the data tyoes, and you will find the errors

SELECT CAST('211609' AS TIME)

returns error