Example - import XML file to SQL Table

/*********************************************
Description: Import xml file to table on SQL Server

2022-11-12 created 

Test.xml

<ROOT>
    <RecordTotal><2></RecordTotal><2>
    <FileDate><2022-11-10></FileDate><2>
    <ITEM>
        <FIELD1>1<FIELD1>
        <FIELD2>test1<FIELD2>
    </ITEM>
    <ITEM>
        <FIELD1>2<FIELD1>
        <FIELD2>test2<FIELD2>
    </ITEM>
</ROOT>

*********************************************/ 

use ETL

--select ('D:\ETL\Test.xml')
declare @XML as XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'D:\ETL\Test.xml', SINGLE_BLOB) AS T(MY_XML)
--select @XML
TRUNCATE TABLE tb_Import
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
Insert into tb_Import
SELECT 1 AS ExecID, 20220122 as ExecEffectiveDate, *
FROM OPENXML(@hDoc, 'Root/Item')
WITH 
(
RecordTotal int '../RecordTotal'
,FileDate [varchar](50) '../FileDate'
,FIELD1 int 'FIELD1'
,FIELD2 [varchar](50) 'FIELD2'

)
EXEC sp_xml_removedocument @hDoc
select * from tb_Import