Import XML into SQL server

[jeffery2110]

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:Envelope
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <GetReportNamesResponse
            xmlns="http://cypad.net/">
            <GetReportNamesResult>
                <xs:schema id="NewDataSet"
                    xmlns=""
                    xmlns:xs="http://www.w3.org/2001/XMLSchema"
                    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
                    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="ReportNames" msdata:UseCurrentLocale="true">
                        <xs:complexType>
                            <xs:choice minOccurs="0" maxOccurs="unbounded">
                                <xs:element name="ReportNames">
                                    <xs:complexType>
                                        <xs:sequence>
                                            <xs:element name="Report_x0020_Names" type="xs:string" minOccurs="0" />
                                        </xs:sequence>
                                    </xs:complexType>
                                </xs:element>
                            </xs:choice>
                        </xs:complexType>
                    </xs:element>
                </xs:schema>
                <diffgr:diffgram
                    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
                    xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
                    <DocumentElement
                        xmlns="">
                        <ReportNames diffgr:id="ReportNames1" msdata:rowOrder="0">
                            <Report_x0020_Names>DataCollectionMealNumbers</Report_x0020_Names>
                        </ReportNames>
                        <ReportNames diffgr:id="ReportNames2" msdata:rowOrder="1">
                            <Report_x0020_Names>MealSelectionMealsTaken</Report_x0020_Names>
                        </ReportNames>
                        <ReportNames diffgr:id="ReportNames3" msdata:rowOrder="2">
                            <Report_x0020_Names>MealSelectionPreOrders</Report_x0020_Names>
                        </ReportNames>
                        <ReportNames diffgr:id="ReportNames4" msdata:rowOrder="3">
                            <Report_x0020_Names>SiteRollNumbers</Report_x0020_Names>
                        </ReportNames>
                        <ReportNames diffgr:id="ReportNames5" msdata:rowOrder="4">
                            <Report_x0020_Names>StaffHours</Report_x0020_Names>
                        </ReportNames>
                        <ReportNames diffgr:id="ReportNames6" msdata:rowOrder="5">
                            <Report_x0020_Names>StockItemsOrderedDelivered</Report_x0020_Names>
                        </ReportNames>
                        <ReportNames diffgr:id="ReportNames7" msdata:rowOrder="6">
                            <Report_x0020_Names>Stocktake</Report_x0020_Names>
                        </ReportNames>
                        <ReportNames diffgr:id="ReportNames8" msdata:rowOrder="7">
                            <Report_x0020_Names>StockTemplateItems</Report_x0020_Names>
                        </ReportNames>
                    </DocumentElement>
                </diffgr:diffgram>
            </GetReportNamesResult>
        </GetReportNamesResponse>
    </soap:Body>
</soap:Envelope>

I tried to use the method shown in :

https://www.mssqltips.com/sqlservertip/5707/simple-way-to-import-xml-data-into-sql-server-with-tsql/

and I code in SQLSMS 18:

INSERT INTO [dbo].[TestReportName] (ReportName)

SELECT
   MY_XML.Names.query('Report_x0020_Names').value('.', 'VARCHAR(20)')

FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\Sample Database\response.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('soap:Envelop/soap:Body/GetReportNamesResponse/GetReportNamesResult/diffgr:diffgram/DocumentElement/ReportNames') AS MY_XML (Names);

trying to put all the data from Report_x0020_Names into the column (ReportName) in table [TestReportName].

However, I have got an error message:

Msg 2229, Level 16, State 1, Line 8
XQuery [T.MY_XML.nodes()]: The name "soap" does not denote a namespace.

Any suggestion here? Please help thanks! 

[#2 · Accepted solution]

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);

[#3]

Thanks so much @bake13, it works fine!