SQL Server

XML Add Child Tree

Demonstrates the Xml.AddChildTree method. SQL Server

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

    -- Build the following XML:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
    --     <soap12:Body>
    --         <cteDadosMsg xmlns="http://www.portalfiscal.inf.br/cte/wsdl/CTeStatusServicoV4">
    -- 	   </cteDadosMsg>
    --     </soap12:Body>
    -- </soap12:Envelope>

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xml, 'Tag', 'soap12:Envelope'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:xsd', 'http://www.w3.org/2001/XMLSchema'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:soap12', 'http://www.w3.org/2003/05/soap-envelope'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soap12:Body|cteDadosMsg', 1, 'xmlns', 'http://www.portalfiscal.inf.br/cte/wsdl/CTeStatusServicoV4'

    -- We want to add the following XML as a sub-tree under the "cteDadosMsg" element.

    -- <consStatServCTe versao="4.00" xmlns="http://www.portalfiscal.inf.br/cte">
    --     <tpAmb>2</tpAmb>
    --     <cUF>43</cUF>
    --     <xServ>STATUS</xServ>
    -- </consStatServCTe>

    -- Build the XML to be added as a child tree.

    DECLARE @xml2 int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml2 OUT

    EXEC sp_OASetProperty @xml2, 'Tag', 'consStatServCTe'
    EXEC sp_OAMethod @xml2, 'AddAttribute', @success OUT, 'versao', '4.00'
    EXEC sp_OAMethod @xml2, 'AddAttribute', @success OUT, 'xmlns', 'http://www.portalfiscal.inf.br/cte'
    EXEC sp_OAMethod @xml2, 'UpdateChildContent', NULL, 'tpAmb', '2'
    EXEC sp_OAMethod @xml2, 'UpdateChildContent', NULL, 'cUF', '43'
    EXEC sp_OAMethod @xml2, 'UpdateChildContent', NULL, 'xServ', 'STATUS'

    -- Add the child tree at the desired location:
    -- Temporarily point to the location where we wish to add the child tree.
    EXEC sp_OAMethod @xml, 'FindChild2', @success OUT, 'soap12:Body|cteDadosMsg'
    IF @success = 0
      BEGIN
        -- Restore current location to the root.
        EXEC sp_OAMethod @xml, 'GetRoot2', NULL
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @xml2
        RETURN
      END

    EXEC sp_OAMethod @xml, 'AddChildTree', @success OUT, @xml2
    -- Restore the current location to the root.
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL

    -- You can see now that the XML contain the child tree:

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
    --     <soap12:Body>
    --         <cteDadosMsg xmlns="http://www.portalfiscal.inf.br/cte/wsdl/CTeStatusServicoV4">
    --             <consStatServCTe versao="4.00" xmlns="http://www.portalfiscal.inf.br/cte">
    --                 <tpAmb>2</tpAmb>
    --                 <cUF>43</cUF>
    --                 <xServ>STATUS</xServ>
    --             </consStatServCTe>
    --         </cteDadosMsg>
    --     </soap12:Body>
    -- </soap12:Envelope>

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @xml2


END
GO