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

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    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