(SQL Server) XML SearchForAttribute Method

Demonstrates the SearchForAttribute method.

The input XML, available at http://www.chilkatsoft.com/data/fruitSearch.xml, is this:

<root>
    <searchRoot>
        <fruit color="red">apple</fruit>
        <fruit color="green">pear</fruit>
        <veg color="orange">carrot</veg>
        <meat animal="cow">beef</meat>
        <xyz>
            <fruit color="blue">blueberry</fruit>
            <veg color="green">broccoli</veg>
        </xyz>
        <fruit color="purple">grape</fruit>
        <cheese color="yellow">cheddar</cheese>
    </searchRoot>
    <fruit color="red">strawberry</fruit>
    <fruit color="orange">peach</fruit>
</root>

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @xSearchRoot int

    DECLARE @xBeginAfter int

    DECLARE @xFound int

    DECLARE @success int
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'qa_data/xml/fruitSearch.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- Search the sub-tree rooted at "searchRoot"
    EXEC sp_OAMethod @xml, 'FindChild', @xSearchRoot OUT, 'searchRoot'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'searchRoot not found, searching from root.'
        EXEC sp_OAMethod @xml, 'GetRoot', @xSearchRoot OUT
      END

    -- Search for all "fruit" nodes having a color attribute
    -- where the name of the color ends in "e"
    EXEC sp_OAMethod @xSearchRoot, 'GetSelf', @xBeginAfter OUT
    EXEC sp_OAMethod @xSearchRoot, 'SearchForAttribute', @xFound OUT, @xBeginAfter, 'fruit', 'color', '*e'
    EXEC sp_OAGetProperty @xSearchRoot, 'LastMethodSuccess', @iTmp0 OUT
    WHILE (@iTmp0 = 1)
      BEGIN

        EXEC sp_OAGetProperty @xFound, 'Content', @sTmp0 OUT

        EXEC sp_OAMethod @xFound, 'GetAttrValue', @sTmp1 OUT, 'color'
        PRINT @sTmp0 + ': ' + @sTmp1

        EXEC @hr = sp_OADestroy @xBeginAfter

        SELECT @xBeginAfter = @xFound
        EXEC sp_OAMethod @xSearchRoot, 'SearchForAttribute', @xFound OUT, @xBeginAfter, 'fruit', 'color', '*e'
      END

    -- The correct output is:
    -- grape: purple
    -- blueberry: blue

    EXEC @hr = sp_OADestroy @xBeginAfter


    PRINT '--------------------------'

    -- ---------------------------------------------------------------------------------
    -- Now do the same, but instead use SearchForAttribute2
    -- which updates the internal reference of the caller instead
    -- of returning the found node.
    DECLARE @xSearch int

    EXEC sp_OAMethod @xSearchRoot, 'GetSelf', @xBeginAfter OUT
    EXEC sp_OAMethod @xSearchRoot, 'GetSelf', @xSearch OUT

    EXEC sp_OAMethod @xSearch, 'SearchForAttribute2', @success OUT, @xBeginAfter, 'fruit', 'color', '*e'
    WHILE @success = 1
      BEGIN

        EXEC sp_OAGetProperty @xSearch, 'Content', @sTmp0 OUT

        EXEC sp_OAMethod @xSearch, 'GetAttrValue', @sTmp1 OUT, 'color'
        PRINT @sTmp0 + ': ' + @sTmp1

        -- Copy the internal references so that the next search
        -- begins after the found node.
        EXEC sp_OAMethod @xBeginAfter, 'CopyRef', NULL, @xSearch
        EXEC sp_OAMethod @xSearch, 'CopyRef', NULL, @xSearchRoot

        EXEC sp_OAMethod @xSearch, 'SearchForAttribute2', @success OUT, @xBeginAfter, 'fruit', 'color', '*e'
      END

    EXEC @hr = sp_OADestroy @xSearch

    EXEC @hr = sp_OADestroy @xBeginAfter

    EXEC @hr = sp_OADestroy @xSearchRoot


    EXEC @hr = sp_OADestroy @xml


END
GO