(SQL Server) Scrub Xml

Demonstrates the Scrub method for some common XML cleaning needs.

Note: This example requires Chilkat v9.5.0.77 or greater.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- The Scrub method has options for trimming whitespace from the ends of attributes and content,
    -- trimming inside whitespace, and for case conversion for tags and attribute names.

    -- Trimming inside whitespace is defined as replacing CR, LF, and tab chars with SPACE chars, and 
    -- removing extra SPACE chars so that at most one SPACE separates non-SPACE chars.

    -- For example, load the following XML:

    DECLARE @s nvarchar(4000)
    SELECT @s = '<xyz><td class=" colHoldingPercent col7" style="border-bottom-color: rgb(221, 221, 221);               border-image-outset: 0;  "> abc' + CHAR(13) + CHAR(10) + '123    456</td></xyz>'

    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 @success int
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @s

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- The XML before scrubbing is:

    -- <xyz>
    --     <td class=" colHoldingPercent col7" style="border-bottom-color: rgb(221, 221, 221);               border-image-outset: 0;  ">abc
    -- 123    456</td>
    -- </xyz>

    EXEC sp_OAMethod @xml, 'Scrub', NULL, 'AttrTrimEnds,AttrTrimInside,ContentTrimEnds,ContentTrimInside'
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- The XML after scrubbing is:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <xyz>
    --     <td class="colHoldingPercent col7" style="border-bottom-color: rgb(221, 221, 221); border-image-outset: 0;">abc 123 456</td>
    -- </xyz>

    EXEC @hr = sp_OADestroy @xml


END
GO