SQL Server

XML Add Child Tree

Demonstrates the Xml.AddChildTree method. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    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