(SQL Server) Auto-Trim XML Content when Loading

This example explains the "autoTrim" argument that is passed to a method such as LoadXml2.

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)
    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

    -- If autoTrim is 1, then the content inside an leaf element is trimmed.
    -- For example:
    DECLARE @autoTrim int
    SELECT @autoTrim = 1
    EXEC sp_OAMethod @xml, 'LoadXml2', @success OUT, '<abc><xyz>  123   </xyz></abc>', @autoTrim
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- Output is:
    -- (notice the SPACE chars before and after "xyz" are trimmed)

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <abc>
    --     <xyz>123</xyz>
    -- </abc>

    -- If autoTrim is 0, then the content inside leaf elements are not trimmed.
    SELECT @autoTrim = 0
    EXEC sp_OAMethod @xml, 'LoadXml2', @success OUT, '<abc><xyz>  123   </xyz></abc>', @autoTrim
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- Output is:

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <abc>
    --     <xyz>  123   </xyz>
    -- </abc>

    -- --------------------------------------------------------------------
    -- The EmitCompact property controls whether XML is emitted indented (pretty-printed)
    -- or compact.  For example:

    -- Auto-trim + emit compact:
    SELECT @autoTrim = 1
    EXEC sp_OAMethod @xml, 'LoadXml2', @success OUT, '<abc><xyz>  123   </xyz></abc>', @autoTrim
    EXEC sp_OASetProperty @xml, 'EmitCompact', 1
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- Output is:

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <abc><xyz>123</xyz></abc>

    -- No Auto-trim + emit compact:
    SELECT @autoTrim = 0
    EXEC sp_OAMethod @xml, 'LoadXml2', @success OUT, '<abc><xyz>  123   </xyz></abc>', @autoTrim
    EXEC sp_OASetProperty @xml, 'EmitCompact', 1
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- Output is:

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <abc><xyz>  123   </xyz></abc>

    EXEC @hr = sp_OADestroy @xml


END
GO