SQL Server

XML Tree Traversal Order for Search* Methods

See more XML Examples

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>

SQL Server

-- 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 @success int
    SELECT @success = 0

    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

    -- 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
        EXEC @hr = sp_OADestroy @xml
        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:
    EXEC sp_OAMethod @xml, 'GetSelf', @xBeginAfter OUT
    EXEC sp_OAMethod @xml, 'SearchAllForContent', @xFound OUT, @xBeginAfter, '*croc*'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    WHILE (@iTmp0 = 1)
      BEGIN

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

        EXEC @hr = sp_OADestroy @xBeginAfter

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

    EXEC @hr = sp_OADestroy @xBeginAfter


    EXEC @hr = sp_OADestroy @xml


END
GO