Import XML into SQL server

Hi all, I am trying to update some tables from some SOAP API responses. One of the XML responses stored in C:\Sample Database\response.xml: <?xml version="1.0" encoding="utf-8"?> <soap:Envelop...

bake13

Aug 16, 2022

Hi jeffery2110 -- See if the code below helps you get started. Take care.

SELECT
   MY_XML.Names.query('Report_x0020_Names').value('.', 'NVARCHAR(96)') AS ReportName
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK '/home/fourthcoffeedba/info.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('//./ReportNames') AS MY_XML (Names);

bake13

Microsoft

Aug 16, 2022

Hi jeffery2110 -- See if the code below helps you get started. Take care.

SELECT
   MY_XML.Names.query('Report_x0020_Names').value('.', 'NVARCHAR(96)') AS ReportName
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK '/home/fourthcoffeedba/info.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('//./ReportNames') AS MY_XML (Names);

jeffery2110

Copper Contributor

Aug 16, 2022

Thanks so much bake13, it works fine!

Forum Discussion

Import XML into SQL server

Resources