none
Parse Soap XML Response and Insert into a table RRS feed

  • Question

  • I need to parse the below  Soap XML Response values into a database table.  Can someone point me in the right direction please?

    <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>
          <GetItemsResponse xmlns="https://www.test.net/">
             <GetItemsResult>
                <acXML lang="en-us" xmlns="https://www.test.net/acXMLSchema.xsd">
                   <Header>
                      <From>
                         <Company>test</Company>
                         <Identity>test</Identity>
                         <DatabaseName>test</DatabaseName>
                      </From>
                   </Header>
                   <Request>
                      <ItemRequest ReqType="GET">
                         <Code>99198U</Code>
                         <Description1>2.5X1.5 Z-ULT 4000T FL.GREEN 100009</Description1>
                         <Description2/>
                         <ItemType>0</ItemType>
                         <Substitute>False</Substitute>
                         <PackQty>1</PackQty>
                         <OrderQty>1</OrderQty>
                         <AvgUnitCost>0.0000</AvgUnitCost>
                         <IssueCost>0.0000</IssueCost>
                         <Cost>0.0000</Cost>
                         <Vendor>BLDR</Vendor>
                         <VendorItem/>
                         <Mfg/>
                         <MfgItem/>
                         <Class/>
                         <LastReview/>
                         <MaxSysQty>0</MaxSysQty>
                         <Notes/>
                         <Picture/>
                         <Kit>False</Kit>
                         <Physical>False</Physical>
                         <ItemRef/>
                         <ItemGroup/>
                         <AccountNum/>
                         <FOD>False</FOD>
                         <MaxFill>False</MaxFill>
                         <LOTControl>False</LOTControl>
                         <RefDifPQty>False</RefDifPQty>
                         <ABCClass/>
                         <CField1>RL</CField1>
                         <CField2/>
                         <CField3/>
                         <CField4/>
                         <CField5/>
                         <CField6/>
                         <CField7/>
                         <CField8/>
                         <CField9/>
                         <CField10/>
                         <CField11/>
                         <CField12/>
                         <Serialize>False</Serialize>
                         <PClass>D</PClass>
                         <StandardPrice>0</StandardPrice>
                         <StandardPrDt/>
                         <AdjStdPrice>0</AdjStdPrice>
                         <AdjStdPrDt/>
                         <Weigh>False</Weigh>
                         <Weight>0.0</Weight>
                         <UOM>lb</UOM>
                      </ItemRequest>
                      <ItemRequest ReqType="GET">
                         <Code>ZAD011938</Code>
                         <Description1>ADAPTOR #221-502 #2 TM-SMITH TAP</Description1>
                         <Description2/>
                         <ItemType>0</ItemType>
                         <Substitute>False</Substitute>
                         <PackQty>1</PackQty>
                         <OrderQty>1</OrderQty>
                         <AvgUnitCost>255.5000</AvgUnitCost>
                         <IssueCost>255.5000</IssueCost>
                         <Cost>0.0000</Cost>
                         <Vendor>BHID-N</Vendor>
                         <VendorItem/>
                         <Mfg/>
                         <MfgItem/>
                         <Class/>
                         <LastReview/>
                         <MaxSysQty>0</MaxSysQty>
                         <Notes/>
                         <Picture/>
                         <Kit>False</Kit>
                         <Physical>False</Physical>
                         <ItemRef/>
                         <ItemGroup/>
                         <AccountNum/>
                         <FOD>False</FOD>
                         <MaxFill>False</MaxFill>
                         <LOTControl>False</LOTControl>
                         <RefDifPQty>False</RefDifPQty>
                         <ABCClass/>
                         <CField1>EA</CField1>
                         <CField2/>
                         <CField3/>
                         <CField4/>
                         <CField5/>
                         <CField6/>
                         <CField7/>
                         <CField8/>
                         <CField9/>
                         <CField10/>
                         <CField11/>
                         <CField12/>
                         <Serialize>False</Serialize>
                         <PClass>D</PClass>
                         <StandardPrice>0</StandardPrice>
                         <StandardPrDt/>
                         <AdjStdPrice>0</AdjStdPrice>
                         <AdjStdPrDt/>
                         <Weigh>False</Weigh>
                         <Weight>0.0</Weight>
                         <UOM>lb</UOM>
                      </ItemRequest>
                   </Request>
                </acXML>
             </GetItemsResult>
          </GetItemsResponse>
       </soap:Body>
    </soap:Envelope>


    sql_guy_1982

    Sunday, December 22, 2019 11:54 PM

Answers

  • Hi sql_guy_1982,

    The soap response has many namespaces declarations. You need to specify them in SQL as well as all required XML elements with corresponding SQL Server data types.

    Check it out below how to shred the XML.

    SQL:

    -- DDL and sample data population, start
    DECLARE @xml XML = N'<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>
    		<GetItemsResponse xmlns="https://www.test.net/">
    			<GetItemsResult>
    				<acXML lang="en-us" xmlns="https://www.test.net/acXMLSchema.xsd">
    					<Header>
    						<From>
    							<Company>test</Company>
    							<Identity>test</Identity>
    							<DatabaseName>test</DatabaseName>
    						</From>
    					</Header>
    					<Request>
    						<ItemRequest ReqType="GET">
    							<Code>99198U</Code>
    							<Description1>2.5X1.5 Z-ULT 4000T FL.GREEN 100009</Description1>
    							<Description2/>
    							<ItemType>0</ItemType>
    							<Substitute>False</Substitute>
    							<PackQty>1</PackQty>
    							<OrderQty>1</OrderQty>
    							<AvgUnitCost>0.0000</AvgUnitCost>
    							<IssueCost>0.0000</IssueCost>
    							<Cost>0.0000</Cost>
    							<Vendor>BLDR</Vendor>
    							<VendorItem/>
    							<Mfg/>
    							<MfgItem/>
    							<Class/>
    							<LastReview/>
    							<MaxSysQty>0</MaxSysQty>
    							<Notes/>
    							<Picture/>
    							<Kit>False</Kit>
    							<Physical>False</Physical>
    							<ItemRef/>
    							<ItemGroup/>
    							<AccountNum/>
    							<FOD>False</FOD>
    							<MaxFill>False</MaxFill>
    							<LOTControl>False</LOTControl>
    							<RefDifPQty>False</RefDifPQty>
    							<ABCClass/>
    							<CField1>RL</CField1>
    							<CField2/>
    							<CField3/>
    							<CField4/>
    							<CField5/>
    							<CField6/>
    							<CField7/>
    							<CField8/>
    							<CField9/>
    							<CField10/>
    							<CField11/>
    							<CField12/>
    							<Serialize>False</Serialize>
    							<PClass>D</PClass>
    							<StandardPrice>0</StandardPrice>
    							<StandardPrDt/>
    							<AdjStdPrice>0</AdjStdPrice>
    							<AdjStdPrDt/>
    							<Weigh>False</Weigh>
    							<Weight>0.0</Weight>
    							<UOM>lb</UOM>
    						</ItemRequest>
    						<ItemRequest ReqType="GET">
    							<Code>ZAD011938</Code>
    							<Description1>ADAPTOR #221-502 #2 TM-SMITH TAP</Description1>
    							<Description2/>
    							<ItemType>0</ItemType>
    							<Substitute>False</Substitute>
    							<PackQty>1</PackQty>
    							<OrderQty>1</OrderQty>
    							<AvgUnitCost>255.5000</AvgUnitCost>
    							<IssueCost>255.5000</IssueCost>
    							<Cost>0.0000</Cost>
    							<Vendor>BHID-N</Vendor>
    							<VendorItem/>
    							<Mfg/>
    							<MfgItem/>
    							<Class/>
    							<LastReview/>
    							<MaxSysQty>0</MaxSysQty>
    							<Notes/>
    							<Picture/>
    							<Kit>False</Kit>
    							<Physical>False</Physical>
    							<ItemRef/>
    							<ItemGroup/>
    							<AccountNum/>
    							<FOD>False</FOD>
    							<MaxFill>False</MaxFill>
    							<LOTControl>False</LOTControl>
    							<RefDifPQty>False</RefDifPQty>
    							<ABCClass/>
    							<CField1>EA</CField1>
    							<CField2/>
    							<CField3/>
    							<CField4/>
    							<CField5/>
    							<CField6/>
    							<CField7/>
    							<CField8/>
    							<CField9/>
    							<CField10/>
    							<CField11/>
    							<CField12/>
    							<Serialize>False</Serialize>
    							<PClass>D</PClass>
    							<StandardPrice>0</StandardPrice>
    							<StandardPrDt/>
    							<AdjStdPrice>0</AdjStdPrice>
    							<AdjStdPrDt/>
    							<Weigh>False</Weigh>
    							<Weight>0.0</Weight>
    							<UOM>lb</UOM>
    						</ItemRequest>
    					</Request>
    				</acXML>
    			</GetItemsResult>
    		</GetItemsResponse>
    	</soap:Body>
    </soap:Envelope>';
    
    DECLARE @tbl TABLE (
    	ID INT IDENTITY PRIMARY KEY
    	, RequestType VARCHAR(10)  NULL
    	, [Code] VARCHAR(30) NULL
    	, [Description1] VARCHAR(100) NULL
    	, [ItemType] INT NULL
    	, [IssueCost] MONEY NULL
    	, [Vendor] VARCHAR(30) NULL
    	, [Kit] BIT NULL
    );
    -- DDL and sample data population, end
    
    ;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' as [soap]
    	, 'https://www.test.net/' as [ns1]
    	, DEFAULT 'https://www.test.net/acXMLSchema.xsd')
    INSERT INTO @tbl (RequestType, Code, Description1, ItemType, IssueCost, Vendor, Kit)
    SELECT c.value('@ReqType', 'VARCHAR(10)') AS [RequestType]
       , c.value('(Code/text())[1]', 'VARCHAR(30)') AS [Code]
       , c.value('(Description1/text())[1]', 'VARCHAR(100)') AS [Description1]
       , c.value('(ItemType/text())[1]', 'INT') AS [ItemType]
       , c.value('(IssueCost/text())[1]', 'MONEY') AS [IssueCost]
       , c.value('(Vendor/text())[1]', 'VARCHAR(30)') AS [Vendor]
       , c.value('(Kit/text())[1]', 'BIT') AS [Kit]
    -- add here the rest of the required XML elements
    FROM @xml.nodes('/soap:Envelope/soap:Body/ns1:GetItemsResponse/ns1:GetItemsResult/acXML/Request/ItemRequest') AS t(c);
    
    -- test
    SELECT * FROM @tbl;
    Monday, December 23, 2019 1:58 AM

All replies

  • Hi sql_guy_1982,

    The soap response has many namespaces declarations. You need to specify them in SQL as well as all required XML elements with corresponding SQL Server data types.

    Check it out below how to shred the XML.

    SQL:

    -- DDL and sample data population, start
    DECLARE @xml XML = N'<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>
    		<GetItemsResponse xmlns="https://www.test.net/">
    			<GetItemsResult>
    				<acXML lang="en-us" xmlns="https://www.test.net/acXMLSchema.xsd">
    					<Header>
    						<From>
    							<Company>test</Company>
    							<Identity>test</Identity>
    							<DatabaseName>test</DatabaseName>
    						</From>
    					</Header>
    					<Request>
    						<ItemRequest ReqType="GET">
    							<Code>99198U</Code>
    							<Description1>2.5X1.5 Z-ULT 4000T FL.GREEN 100009</Description1>
    							<Description2/>
    							<ItemType>0</ItemType>
    							<Substitute>False</Substitute>
    							<PackQty>1</PackQty>
    							<OrderQty>1</OrderQty>
    							<AvgUnitCost>0.0000</AvgUnitCost>
    							<IssueCost>0.0000</IssueCost>
    							<Cost>0.0000</Cost>
    							<Vendor>BLDR</Vendor>
    							<VendorItem/>
    							<Mfg/>
    							<MfgItem/>
    							<Class/>
    							<LastReview/>
    							<MaxSysQty>0</MaxSysQty>
    							<Notes/>
    							<Picture/>
    							<Kit>False</Kit>
    							<Physical>False</Physical>
    							<ItemRef/>
    							<ItemGroup/>
    							<AccountNum/>
    							<FOD>False</FOD>
    							<MaxFill>False</MaxFill>
    							<LOTControl>False</LOTControl>
    							<RefDifPQty>False</RefDifPQty>
    							<ABCClass/>
    							<CField1>RL</CField1>
    							<CField2/>
    							<CField3/>
    							<CField4/>
    							<CField5/>
    							<CField6/>
    							<CField7/>
    							<CField8/>
    							<CField9/>
    							<CField10/>
    							<CField11/>
    							<CField12/>
    							<Serialize>False</Serialize>
    							<PClass>D</PClass>
    							<StandardPrice>0</StandardPrice>
    							<StandardPrDt/>
    							<AdjStdPrice>0</AdjStdPrice>
    							<AdjStdPrDt/>
    							<Weigh>False</Weigh>
    							<Weight>0.0</Weight>
    							<UOM>lb</UOM>
    						</ItemRequest>
    						<ItemRequest ReqType="GET">
    							<Code>ZAD011938</Code>
    							<Description1>ADAPTOR #221-502 #2 TM-SMITH TAP</Description1>
    							<Description2/>
    							<ItemType>0</ItemType>
    							<Substitute>False</Substitute>
    							<PackQty>1</PackQty>
    							<OrderQty>1</OrderQty>
    							<AvgUnitCost>255.5000</AvgUnitCost>
    							<IssueCost>255.5000</IssueCost>
    							<Cost>0.0000</Cost>
    							<Vendor>BHID-N</Vendor>
    							<VendorItem/>
    							<Mfg/>
    							<MfgItem/>
    							<Class/>
    							<LastReview/>
    							<MaxSysQty>0</MaxSysQty>
    							<Notes/>
    							<Picture/>
    							<Kit>False</Kit>
    							<Physical>False</Physical>
    							<ItemRef/>
    							<ItemGroup/>
    							<AccountNum/>
    							<FOD>False</FOD>
    							<MaxFill>False</MaxFill>
    							<LOTControl>False</LOTControl>
    							<RefDifPQty>False</RefDifPQty>
    							<ABCClass/>
    							<CField1>EA</CField1>
    							<CField2/>
    							<CField3/>
    							<CField4/>
    							<CField5/>
    							<CField6/>
    							<CField7/>
    							<CField8/>
    							<CField9/>
    							<CField10/>
    							<CField11/>
    							<CField12/>
    							<Serialize>False</Serialize>
    							<PClass>D</PClass>
    							<StandardPrice>0</StandardPrice>
    							<StandardPrDt/>
    							<AdjStdPrice>0</AdjStdPrice>
    							<AdjStdPrDt/>
    							<Weigh>False</Weigh>
    							<Weight>0.0</Weight>
    							<UOM>lb</UOM>
    						</ItemRequest>
    					</Request>
    				</acXML>
    			</GetItemsResult>
    		</GetItemsResponse>
    	</soap:Body>
    </soap:Envelope>';
    
    DECLARE @tbl TABLE (
    	ID INT IDENTITY PRIMARY KEY
    	, RequestType VARCHAR(10)  NULL
    	, [Code] VARCHAR(30) NULL
    	, [Description1] VARCHAR(100) NULL
    	, [ItemType] INT NULL
    	, [IssueCost] MONEY NULL
    	, [Vendor] VARCHAR(30) NULL
    	, [Kit] BIT NULL
    );
    -- DDL and sample data population, end
    
    ;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' as [soap]
    	, 'https://www.test.net/' as [ns1]
    	, DEFAULT 'https://www.test.net/acXMLSchema.xsd')
    INSERT INTO @tbl (RequestType, Code, Description1, ItemType, IssueCost, Vendor, Kit)
    SELECT c.value('@ReqType', 'VARCHAR(10)') AS [RequestType]
       , c.value('(Code/text())[1]', 'VARCHAR(30)') AS [Code]
       , c.value('(Description1/text())[1]', 'VARCHAR(100)') AS [Description1]
       , c.value('(ItemType/text())[1]', 'INT') AS [ItemType]
       , c.value('(IssueCost/text())[1]', 'MONEY') AS [IssueCost]
       , c.value('(Vendor/text())[1]', 'VARCHAR(30)') AS [Vendor]
       , c.value('(Kit/text())[1]', 'BIT') AS [Kit]
    -- add here the rest of the required XML elements
    FROM @xml.nodes('/soap:Envelope/soap:Body/ns1:GetItemsResponse/ns1:GetItemsResult/acXML/Request/ItemRequest') AS t(c);
    
    -- test
    SELECT * FROM @tbl;
    Monday, December 23, 2019 1:58 AM
  • thank you Yitzhak, this did the trick!

    sql_guy_1982

    Monday, December 23, 2019 2:22 AM
  • Hi sql_guy_1982,

    Glad to hear that the proposed solution is working for you.

     

    P.S. Please connect with me on the LinkedIn.

    Monday, December 23, 2019 2:25 AM
  • Yitzhak,

    Yes will do!


    sql_guy_1982

    Monday, December 23, 2019 2:35 AM