(SQL Server) Adding Attributes to an XML Node

Demonstrates how to add attributes to existing XML nodes.

The input XML is this:

<abc>
    <xyz>
        <mmm>123</mmm>
    </xyz>
</abc>

The output XML is this:

<abc a="123">
    <xyz b="456">
        <mmm c="789" d="000">123</mmm>
    </xyz>
</abc>

Download Chilkat XML ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'add_attribute.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  Add an attribute  a="123" to the root node:
    EXEC sp_OAMethod @xml, 'AddAttribute', NULL, 'a', '123'

    --  Navigate to the 1st child.  After calling FirstChild2,
    --  "xml" now references the node with the tag "xyz".
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL
    EXEC sp_OAMethod @xml, 'AddAttribute', NULL, 'b', '456'

    --  Navigate to xyz's first child, which is the node having the
    --  tag "mmm".
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL
    EXEC sp_OAMethod @xml, 'AddAttribute', NULL, 'c', '789'
    EXEC sp_OAMethod @xml, 'AddAttribute', NULL, 'd', '000'

    --  Revert back to the XML document root node
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL

    --  Examine the result:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0
END
GO