Few issues Inserting and filtering XML data to SQL

I’m having a few issues when inserting data from a XML file to a SQL Server database.

1. How can I get the file name dynamically? This file is put on that path with a different number every time but always starts with Idoc and it’s a .xml file. I’m having troubles setting a variable inside OPENROWSET.

2. At the moment I’m getting data from just one path (ZPROD) but I want to be able to insert data from EDI_DC40/DOCNUM instead of ZPROD/PLN_ORDER, I would need another CROSS APPLY but I can’t get it to work.

3. I want to ignore the .000 from QTY, I tried setting it to int but it didn’t work, ‘can’t convert to int’.

4. Is there a way to ignore most of LINENO and just get the last two characters? It’s set to varchar(2) but obviously is taking the first two characters, not the last ones.

Below is my code, I hope is all within the rules and understandable:

Thanks.

<?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>

CREATE TABLE XMLTESTTABLE
(
    PONo int, 
    ASP int, 
    LOTNo varchar(11), 
    EntryDate date, 
    StartDate date, 
    EndDate date, 
    GAS int, 
    PlannedQty varchar(15), 
    LineNum varchar(2), 
    SAPDesc varchar(200), 
    StartTime date, 
    EndTime date
);

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(2)'),
   MY_XML.ZPROD.query('MAKTX').value('.', 'VARCHAR(200)'),
   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:UsersPC_userDocumentsIdoc3899888135.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('ZMPROD01/ZPROD') AS MY_XML (ZPROD);

Answer

Please try the following solution.

• No need to use the .query() method.
• ##3,4 got resolved.
• I took a liberty to handle invalid 240000 time as 23:59:59.
• Last two columns data type is time(0).

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS XMLTESTTABLE;

CREATE TABLE XMLTESTTABLE (
    PONo INT, 
    ASP int, 
    LOTNo varchar(11), 
    EntryDate date, 
    StartDate date, 
    EndDate date, 
    GAS int, 
    PlannedQty varchar(15), 
    LineNum varchar(2), 
    SAPDesc varchar(200), 
    StartTime time(0), 
    EndTime time(0)
);

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]', 'DECIMAL(10,0)') AS [qty]
    , RIGHT(ZPROD.value('(LINENO/text())[1]', 'VARCHAR(10)'), 2) AS [lineno]
    , ZPROD.value('(MAKTX/text())[1]', 'VARCHAR(200)') AS MAKTX
    , TRY_CAST(STUFF(STUFF(ZPROD.value('(START2/text())[1]', 'CHAR(6)'),3,0,':'),6,0,':') AS TIME)
    , TRY_CAST(IIF(STUFF(STUFF(ZPROD.value('(END2/text())[1]', 'CHAR(6)'),3,0,':'),6,0,':')='24:00:00'
        ,'23:59:59',null) AS TIME)
FROM (SELECT TRY_CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'e:TempIdoc3899888135.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('/ZMPROD01/IDOC/ZPROD') AS MY_XML(ZPROD);

-- test
SELECT * FROM dbo.XMLTESTTABLE;