(SQL Server) Find and Update an XML Attribute Value

See more XML Examples

Demonstrates how to find an element in XML with a specified attribute name and then update the attribute value.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Let's say you have 2 XML files with identical structures as shown directly below,
    -- and you wish to update the values in the 1st XML file with those in the 2nd XML file.

    -- <global>
    --     <phrase id="2FADisabled" value="2factor authentication disabled"/>
    --     <phrase id="2FAEnabled" value="2factor authentication enabled"/>
    --     <phrase id="2FAResetFailed" value="Failed to disable two factor authentication"/>
    -- </global>

    -- <global>
    --     <phrase id="2FAResetFailed" value="Failed to reset two factor authentication"/>
    --     <phrase id="2FADisabled" value="2FA authentication disabled"/>
    --     <phrase id="2FAEnabled" value="2FA authentication enabled"/>
    -- </global>

    -- First, initialize each of the Chilkat XML objects.
    -- These could be loaded from files, but we'll just create them manually for this example..

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

    EXEC sp_OASetProperty @xml1, 'Tag', 'global'
    DECLARE @success int
    EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'phrase', 1, 'id', '2FADisabled'
    EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'phrase', 1, 'value', '2factor authentication disabled'
    EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'phrase[1]', 1, 'id', '2FAEnabled'
    EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'phrase[1]', 1, 'value', '2factor authentication enabled'
    EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'phrase[2]', 1, 'id', '2FAResetFailed'
    EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'phrase[2]', 1, 'value', 'Failed to disable two factor authentication'

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

    EXEC sp_OASetProperty @xml2, 'Tag', 'global'
    EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'phrase', 1, 'id', '2FAResetFailed'
    EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'phrase', 1, 'value', 'Failed to reset two factor authentication'
    EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'phrase[1]', 1, 'id', '2FADisabled'
    EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'phrase[1]', 1, 'value', '2FA authentication disabled'
    EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'phrase[2]', 1, 'id', '2FAEnabled'
    EXEC sp_OAMethod @xml2, 'UpdateAttrAt', @success OUT, 'phrase[2]', 1, 'value', '2FA authentication enabled'

    -- Iterate over attribute values in xml2 and update the same in xml1.

    DECLARE @phrase_id nvarchar(4000)

    DECLARE @phrase_value nvarchar(4000)

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

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @xml2, 'NumChildrenHavingTag', @count_i OUT, 'phrase'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @xml2, 'I', @i
        EXEC sp_OAMethod @xml2, 'ChilkatPath', @phrase_id OUT, 'phrase[i]|(id)'
        EXEC sp_OAMethod @xml2, 'ChilkatPath', @phrase_value OUT, 'phrase[i]|(value)'

        -- Notice how the UpdateAttrAt method's 1st argument is a Chilkat path -- which is an expression
        -- to find the location of the XML element to be updated.
        -- Here we create a path of the form "/A/tagName,attributeName,attributeValuePattern", which means
        -- find the direct descendent of xml2 having tag=tagName, with an attribute=attributeName where the attribute value
        -- matches the attributeValuePattern.  If attributeValuePattern does not contain a "*", then it must match directly.
        -- We'll build a Chilkat path such as "/A/phrase,id,2FADisabled"
        EXEC sp_OAMethod @sbPath, 'SetString', @success OUT, '/A/phrase,id,'
        EXEC sp_OAMethod @sbPath, 'Append', @success OUT, @phrase_id

        -- This assumes the corresponding element exists in xml1.
        EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, @sTmp0, 1, 'value', @phrase_value

        SELECT @i = @i + 1
      END

    -- Now see how xml1 has been updated..
    EXEC sp_OAMethod @xml1, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- <global>
    --     <phrase id="2FADisabled" value="2FA authentication disabled"/>
    --     <phrase id="2FAEnabled" value="2FA authentication enabled"/>
    --     <phrase id="2FAResetFailed" value="Failed to reset two factor authentication"/>
    -- </global>

    EXEC @hr = sp_OADestroy @xml1
    EXEC @hr = sp_OADestroy @xml2
    EXEC @hr = sp_OADestroy @sbPath


END
GO