SQL Server

XML Accumulate Tag Content

See more XML Examples

Demonstrates how to use the AccumulateTagContent method of the XML class.

Imagine the XML document for this test contains the following:

<?xml version="1.0" encoding="utf-8"?>
<abc>
	<zzz>The quick brown fox</zzz>
	<xyz>
	    <zzz>hello world!</zzz>
	</xyz>
	<mmm>
	    <zzz>jumped over the lazy dog.</zzz>
	</mmm>
</abc>

The result of accumulating the content for all "zzz" nodes, but skipping subtrees rooted at "xyz" nodes, is the string "The quick brown fox jumped over the lazy dog." 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
    -- 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

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

    -- Accumulate the textual content within the "zzz" nodes of this
    -- XML.  Skip sub-trees rooted at nodes having the tag "xyz".
    -- For the given test XML, the resulting accumulated text should be:
    -- "The quick brown fox jumped over the lazy dog."
    EXEC sp_OAMethod @xml, 'AccumulateTagContent', @sTmp0 OUT, 'zzz', 'xyz'
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xml


END
GO