(SQL Server) Convert HTML to XML with Auto-Correction

Simple HTML to XML conversion. Demonstrates how the HTML is auto-corrected to create well-formed XML. In this example, the closing

is missing. Also, text is encapsulated in nodes with the intent to make it easy for programs to identify and extract the text parts.

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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @htmlToXml int
    -- Use "Chilkat_9_5_0.HtmlToXml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.HtmlToXml', @htmlToXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Indicate the charset of the output XML we'll want.
    EXEC sp_OASetProperty @htmlToXml, 'XmlCharset', 'utf-8'

    -- Set the HTML:
    EXEC sp_OASetProperty @htmlToXml, 'Html', '<html><body><p>This is a test <a href="http://www.chilkatsoft.com/">Chilkat Software</a></body></html>'

    -- Get the XML:
    EXEC sp_OAMethod @htmlToXml, 'ToXml', @sTmp0 OUT
    PRINT @sTmp0

    -- This is the output:
    -- <?xml version="1.0" encoding="utf-8" ?>
    -- 
    -- <root>
    --     <html>
    --         <body>
    --             <p>
    --                 <text>This is a test </text>
    --                 <a href="http://www.chilkatsoft.com/">
    --                     <text>Chilkat Software</text>
    --                 </a>
    --             </p>
    --         </body>
    --     </html>
    -- </root

    EXEC @hr = sp_OADestroy @htmlToXml


END
GO