(SQL Server) XML Tree Traversal Order for Search* Methods

The Chilkat XML API provides a number of Search* methods for locating XML nodes based on criteria. These methods traverse an XML document in a breadth-first order. (See Breadth-First Search).

The XML document used in this example has the following tree structure:

The nodes are traversed in the order: A, B, C, D, E, F, G, H, I, J, K.

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

<a>crocodileA
    <b>crocodileB
        <d>crocodileD</d>
        <e>crocodileE
            <h>crocodileH</h>
            <i>crocodileI</i>
        </e>
    </b>
    <c>crocodileC
        <f>crocodileF</f>
        <g>crocodileG
            <j>crocodileJ</j>
            <k>crocodileK</k>
        </g>
    </c>
</a>

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 @xBeginAfter int

    DECLARE @xFound int

    DECLARE @success int

    --  The sample input XML is available at http://www.chilkatsoft.com/data/searchOrder.xml
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'searchOrder.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  IMPORTANT:  The following loop for iterating over all
    --  matching nodes performs reasonably well for small to
    --  mid-size XML documents or sub-trees.  Performance
    --  can be poor when the sub-tree contains many thousands
    --  of nodes (or more).

    --  NOTE:  The search is always rooted at the calling node.
    --  In this example, it happens to also be the root node of the entire
    --  XML document.  Searches can be performed on sub-trees
    --  within the document by calling the Search* method from
    --  the root node of a sub-tree.

    --  Demonstrate the breadth-first traversal:
    SELECT @xBeginAfter = NULL
    EXEC sp_OAMethod @xml, 'SearchAllForContent', @xFound OUT, @xBeginAfter, '*croc*'
    WHILE Not (@xFound Is NULL )
      BEGIN

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

        PRINT @sTmp0

        SELECT @xBeginAfter = @xFound
        EXEC sp_OAMethod @xml, 'SearchAllForContent', @xFound OUT, @xBeginAfter, '*croc*'
      END


END
GO