(SQL Server) Move XML Subtree to Another XML Document

Demonstrates using the InsertChildTreeBefore method to move a fragment of XML from one document to another.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Source XML is this:

    -- <?xml version='1.0' encoding='UTF-8'?>
    -- <soapenv:Envelope xmlns:"soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    --               <soapenv:Header/>
    --               <soapenv:Body>
    --                             <GetCustomerResponse xmlns="http://www.midoco.de/crm" xmlns:tns="http://www.midoco.de/ws">
    --                                           <CrmCustomer addresseeLine1="Max Mustermann" addresseeLine2="" changingUser="123456" >
    --                                                          <CrmAddress addressId="2225355" addressTypeId="1" checkStatus="O" city="Wien" countryCode="AT" customerId="000071"/>
    --                                                          <CrmPerson birthDay="30" birthMonth="8" birthYear="1977" birthday="1977-08-30T00:00:00.000+02:00" birthdayNotProvided="false"/>
    --                                           </CrmCustomer>
    --                           </GetCustomerResponse>
    --               </soapenv:Body>
    -- </soapenv:Envelope>

    -- Build the source XML.
    DECLARE @srcXml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @srcXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @srcXml, 'Tag', 'soapenv:Envelope'
    DECLARE @success int
    EXEC sp_OAMethod @srcXml, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
    EXEC sp_OAMethod @srcXml, 'UpdateChildContent', NULL, 'soapenv:Header', ''
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse', 1, 'xmlns', 'http://www.midoco.de/crm'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse', 1, 'xmlns:tns', 'http://www.midoco.de/ws'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer', 1, 'addresseeLine1', 'Max Mustermann'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer', 1, 'addresseeLine2', ''
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer', 1, 'changingUser', '123456'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmAddress', 1, 'addressId', '2225355'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmAddress', 1, 'addressTypeId', '1'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmAddress', 1, 'checkStatus', 'O'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmAddress', 1, 'city', 'Wien'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmAddress', 1, 'countryCode', 'AT'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmAddress', 1, 'customerId', '000071'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmPerson', 1, 'birthDay', '30'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmPerson', 1, 'birthMonth', '8'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmPerson', 1, 'birthYear', '1977'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmPerson', 1, 'birthday', '1977-08-30T00:00:00.000+02:00'
    EXEC sp_OAMethod @srcXml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer|CrmPerson', 1, 'birthdayNotProvided', 'false'

    -- Destination XML is this:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    --     <SOAP-ENV:Header>
    --         <m:Credentials xmlns:m="http://www.midoco.de/system">
    --             <m:Login>User</m:Login>
    --             <m:Password>Pass</m:Password>
    --             <m:OrgUnit>ABC</m:OrgUnit>
    --             <m:Locale>de_DE</m:Locale>
    --         </m:MidocoCredentials>
    --     </SOAP-ENV:Header>
    --     <SOAP-ENV:Body>
    --         <SaveCustomerRequest>
    --         </SaveCustomerRequest>
    --     </SOAP-ENV:Body>
    -- </SOAP-ENV:Envelope>

    DECLARE @destXml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @destXml OUT

    EXEC sp_OASetProperty @destXml, 'Tag', 'SOAP-ENV:Envelope'
    EXEC sp_OAMethod @destXml, 'AddAttribute', @success OUT, 'xmlns:SOAP-ENV', 'http://schemas.xmlsoap.org/soap/envelope/'
    EXEC sp_OAMethod @destXml, 'AddAttribute', @success OUT, 'xmlns:SOAP-ENC', 'http://schemas.xmlsoap.org/soap/encoding/'
    EXEC sp_OAMethod @destXml, 'AddAttribute', @success OUT, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @destXml, 'AddAttribute', @success OUT, 'xmlns:xsd', 'http://www.w3.org/2001/XMLSchema'
    EXEC sp_OAMethod @destXml, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Header|m:Credentials', 1, 'xmlns:m', 'http://www.midoco.de/system'
    EXEC sp_OAMethod @destXml, 'UpdateChildContent', NULL, 'SOAP-ENV:Header|m:Credentials|m:Login', 'User'
    EXEC sp_OAMethod @destXml, 'UpdateChildContent', NULL, 'SOAP-ENV:Header|m:Credentials|m:Password', 'Pass'
    EXEC sp_OAMethod @destXml, 'UpdateChildContent', NULL, 'SOAP-ENV:Header|m:Credentials|m:OrgUnit', 'ABC'
    EXEC sp_OAMethod @destXml, 'UpdateChildContent', NULL, 'SOAP-ENV:Header|m:Credentials|m:Locale', 'de_DE'
    EXEC sp_OAMethod @destXml, 'UpdateChildContent', NULL, 'SOAP-ENV:Body|SaveCustomerRequest', ''

    -- We want to move the "CrmCustomer" subtree in the source XML to inside the "SaveCustomerRequest" element in the destination.

    -- Navigate to CrmCustomer
    DECLARE @crmCust int
    EXEC sp_OAMethod @srcXml, 'FindChild', @crmCust OUT, 'soapenv:Body|GetCustomerResponse|CrmCustomer'
    EXEC sp_OAGetProperty @srcXml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Failed to find CrmCustomer element.'
        EXEC @hr = sp_OADestroy @srcXml
        EXEC @hr = sp_OADestroy @destXml
        RETURN
      END

    -- Navigate to SaveCustomerRequest
    DECLARE @crmSaveCust int
    EXEC sp_OAMethod @destXml, 'FindChild', @crmSaveCust OUT, 'SOAP-ENV:Body|SaveCustomerRequest'
    EXEC sp_OAGetProperty @destXml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Failed to find SaveCustomerRequest element.'
        EXEC @hr = sp_OADestroy @srcXml
        EXEC @hr = sp_OADestroy @destXml
        RETURN
      END

    -- Move CrmCustomer tree to SaveCustomerRequest.
    EXEC sp_OAMethod @crmSaveCust, 'InsertChildTreeBefore', NULL, 0, @crmCust

    EXEC @hr = sp_OADestroy @crmCust

    EXEC @hr = sp_OADestroy @crmSaveCust

    -- Look at the resulting destXml.  You can see the CrmCustomer subtree moved to underneath SaveCustomerRequest.
    EXEC sp_OAMethod @destXml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    --     <SOAP-ENV:Header>
    --         <m:Credentials xmlns:m="http://www.midoco.de/system">
    --             <m:Login>User</m:Login>
    --             <m:Password>Pass</m:Password>
    --             <m:OrgUnit>ABC</m:OrgUnit>
    --             <m:Locale>de_DE</m:Locale>
    --         </m:Credentials>
    --     </SOAP-ENV:Header>
    --     <SOAP-ENV:Body>
    --         <SaveCustomerRequest>
    --             <CrmCustomer addresseeLine1="Max Mustermann" addresseeLine2="" changingUser="123456">
    --                 <CrmAddress addressId="2225355" addressTypeId="1" checkStatus="O" city="Wien" countryCode="AT" customerId="000071"/>
    --                 <CrmPerson birthDay="30" birthMonth="8" birthYear="1977" birthday="1977-08-30T00:00:00.000+02:00" birthdayNotProvided="false"/>
    --             </CrmCustomer>
    --         </SaveCustomerRequest>
    --     </SOAP-ENV:Body>
    -- </SOAP-ENV:Envelope>

    -- Look at the resulting srcXml.  The CrmCustomer subtree was removed.
    EXEC sp_OAMethod @srcXml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    --     <soapenv:Header/>
    --     <soapenv:Body>
    --         <GetCustomerResponse xmlns="http://www.midoco.de/crm" xmlns:tns="http://www.midoco.de/ws"/>
    --     </soapenv:Body>
    -- </soapenv:Envelope>

    EXEC @hr = sp_OADestroy @srcXml
    EXEC @hr = sp_OADestroy @destXml


END
GO