SQL Server
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 SQL Server Downloads
-- 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
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
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