(SQL Server) Get FTP Directory Listing as XML

Demonstrates how to call GetXmlDirListing and parse the results.

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes Chilkat Ftp2 to have been previously unlocked.
    -- See Unlock Ftp2 for sample code.

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

    EXEC sp_OASetProperty @ftp, 'Hostname', 'www.my-ftp-server.com'
    EXEC sp_OASetProperty @ftp, 'Username', 'mFtpLogin'
    EXEC sp_OASetProperty @ftp, 'Password', 'myFtpPassword'

    -- Connect to the FTP server.
    DECLARE @success int
    EXEC sp_OAMethod @ftp, 'ConnectOnly', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- Authenticate with the FTP server.
    EXEC sp_OAMethod @ftp, 'LoginAfterConnectOnly', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- Retrieve (in XML format) the HOME directory of this FTP account.
    DECLARE @xmlListing nvarchar(4000)
    EXEC sp_OAMethod @ftp, 'GetXmlDirListing', @xmlListing OUT, '*.*'
    EXEC sp_OAGetProperty @ftp, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- Now load the XML and parse it..
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @xmlListing
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- Iterate over the XML...
    DECLARE @i int
    SELECT @i = 0
    DECLARE @numEntries int
    EXEC sp_OAGetProperty @xml, 'NumChildren', @numEntries OUT
    WHILE @i < @numEntries
      BEGIN
        DECLARE @xEntry int
        EXEC sp_OAMethod @xml, 'GetChild', @xEntry OUT, @i
        EXEC sp_OAMethod @xEntry, 'TagEquals', @iTmp0 OUT, 'dir'
        IF @iTmp0 = 1
          BEGIN

            EXEC sp_OAGetProperty @xEntry, 'Content', @sTmp0 OUT
            PRINT 'Directory: ' + @sTmp0
          END
        ELSE
          BEGIN
            DECLARE @sz int
            EXEC sp_OAMethod @xEntry, 'GetChildIntValue', @sz OUT, 'size'

            EXEC sp_OAMethod @xEntry, 'GetChildContent', @sTmp0 OUT, 'name'

            PRINT 'File: ' + @sTmp0 + ', size: ' + @sz
            DECLARE @xLastMod int
            EXEC sp_OAMethod @xEntry, 'FindChild', @xLastMod OUT, 'lastModTime'
            EXEC sp_OAGetProperty @xEntry, 'LastMethodSuccess', @iTmp0 OUT
            IF @iTmp0 = 1
              BEGIN
                DECLARE @month int
                EXEC sp_OAMethod @xLastMod, 'GetAttrValueInt', @month OUT, 'm'
                DECLARE @year int
                EXEC sp_OAMethod @xLastMod, 'GetAttrValueInt', @year OUT, 'y'
                DECLARE @day int
                EXEC sp_OAMethod @xLastMod, 'GetAttrValueInt', @day OUT, 'd'



                PRINT '    YYYY-MM-DD: ' + @year + '-' + @month + '-' + @day
                EXEC @hr = sp_OADestroy @xLastMod

              END
          END

        EXEC @hr = sp_OADestroy @xEntry

        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @ftp, 'Disconnect', @success OUT


    PRINT 'Success.'

    -- Sample XML directory listing:
    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <remoteDir>
    --     <dir>Desktop</dir>
    --     <dir>Documents</dir>
    --     <dir>Downloads</dir>
    --     <dir>Music</dir>
    --     <dir>Pictures</dir>
    --     <dir>Public</dir>
    --     <dir>Templates</dir>
    --     <dir>Videos</dir>
    --     <file>
    --         <name>c.py</name>
    --         <size>1244</size>
    --         <lastModTime full="20151009-000000" y="2015" d="9" m="10" hh="0" mm="0" ss="0" />
    --     </file>
    --     <file>
    --         <name>cacerts_linux</name>
    --         <size>177207</size>
    --         <lastModTime full="20140915-000000" y="2014" d="15" m="9" hh="0" mm="0" ss="0" />
    --     </file>
    --     <file>
    --         <name>empty.txt</name>
    --         <size>0</size>
    --         <lastModTime full="20150917-000000" y="2015" d="17" m="9" hh="0" mm="0" ss="0" />
    --     </file>
    --     <file>
    --         <name>hamlet.xml</name>
    --         <size>279658</size>
    --         <lastModTime full="20160917-084100" y="2016" d="17" m="9" hh="8" mm="41" ss="0" />
    --     </file>
    -- </remoteDir>
    -- 
    -- 

    EXEC @hr = sp_OADestroy @ftp
    EXEC @hr = sp_OADestroy @xml


END
GO