ODBC FTP / SFTP Driver for XML Files
Query XML files over FTP, FTPS, or SFTP.
Query XML files stored in FTP, FTPS and SFTP servers with plain SQL. Connect with your password or private key, read one file or a wildcard set including GZip / Zip files, and use the data in SQL Server (T-SQL) or your BI, ETL and reporting tools, all without writing code.
Included in the Professional edition and above
- Any ODBC-compliant tool
- Runs in your environment
- SQL Server via Data Gateway
- 32-bit and 64-bit

At a glance
What the FTP / SFTP Driver for XML Files can do
Query XML files over FTP, FTPS, or SFTP.
XML files in FTP / SFTP
Query XML files stored on FTP, FTPS and SFTP servers as tables, without downloading them first.
- Connect with Protocol, Server Name, User Name and Password (or key)
- Single file or wildcard
- Sub-folders with
RECURSIVE
Compressed files
Read GZip and Zip compressed files with the FileCompressionType option.
- None, Zip or GZip
- Wildcards across archives
- Same SQL
Familiar SQL
Use WHERE, ORDER BY, GROUP BY and built-in math, string, date and JSON functions on top of the data source.
- Standard SELECT syntax
- Custom functions in the SQL dialect
- Driver options in a WITH clause
Works with the tools you have
Any ODBC-compliant tool can use the DSN: Power BI, Tableau, Qlik, SSRS, Informatica, Excel and SSIS.
- Create a DSN once, reuse it everywhere
- 32-bit and 64-bit ODBC
- Stored in the standard ODBC Data Source Administrator
Use it from code
Connect from Java, C#, Python, PowerShell and more with an ODBC connection string that names the DSN.
- Standard ODBC connection strings
- Parameterized queries
- Same SQL as in the UI
SQL Server via Data Gateway
Query the driver from SQL Server (linked server / OPENQUERY) without installing it on the SQL Server machine, through the Data Gateway.
- No driver install on the server
- Works with SQL Server 2012 through 2025 (32/64 bit)
- Same DSN, same SQL
See all features
- Read XML file data from FTP / SFTP / FTPS site using familiar SQL Query language
- Integrate insight any ODBC Compliant Reporting / ETL tools (e.g. Power BI, Tableau, Qlik, SSRS, Informatica, Excel, SSIS)
- Support for programming languages such as JAVA, C#, Python, PowerShell and more…
- Tight integration with Microsoft SQL Server (With support for Gateway Option – No need to install Driver on Server)
- Familiar SQL Query language support including WHERE, ORDER BY, GROUP BY constructs
- Support for custom math/ string / datetime / JSON functions in SQL query Language
- Support for Array Flattening and Complex Transformations for 2D arrays (See this article )
- Read single or multiple files (wildcard pattern supported e.g. *.json)
- Support for reading Zip and Gzip compressed files (stream mode)
- Support missing columns at the end (Auto fill with null values)
- Option to pivot data (transform columns into rows)
SQL Server 2012–2025
Ways to use it
Pick how you reach your data.
Configure the DSN once, then query it from any ODBC tool or from SQL Server.
Query one XML file.
Set SRC to the path of a file in your server folder.
- Credentials: Protocol, Server Name, User Name and Password (or key)
- SQL on top of the file
- Preview in the UI

Query many XML files at once.
Use a wildcard such as customers*.???, add RECURSIVE for sub-folders and read GZip or Zip files.
- Wildcard patterns
- Sub-folders with RECURSIVE
- GZip / Zip

Query it from SQL Server with Data Gateway.
Expose the driver through the ODBC PowerPack Data Gateway and create a linked server in SQL Server. The driver does not have to be installed on the SQL Server machine.
- Linked server or OPENQUERY
- No driver install on the server
- Works with SQL Server 2012 through 2025 (32/64 bit)

Inside the UI
The driver UI, step by step.
Real screens from the ZappySys ODBC driver configuration.
Create the DSN
Add a user or system DSN and pick the FTP / SFTP Driver for XML Files.
SQL example
Write SQL. Get a flat table.
The driver reads files in FTP, FTPS and SFTP servers, flattens the XML and lets you use WHERE, ORDER BY, GROUP BY and functions on top. Below: a sample document, the SQL query and the rows it returns.
-
Sample XML data
A XML file in your server folder, shown here as a sample.
<Customers> <Customer><CustomerID>C1</CustomerID><CompanyName>Company 1</CompanyName><City>Atlanta</City></Customer> <Customer><CustomerID>C2</CustomerID><CompanyName>Company 2</CompanyName><City>Boston</City></Customer> <Customer><CustomerID>C3</CustomerID><CompanyName>Company 3</CompanyName><City>Atlanta</City></Customer> </Customers> -
Run the SQL query
SQL Query
SELECT CustomerID, CompanyName FROM $ WHERE City = 'Atlanta' ORDER BY CompanyName WITH ( SRC = '/in/customers*.xml', Filter = '$.Customers.Customer[*]' )Paste the statement into the driver’s Query Editor, or run it from any ODBC tool or language.
WITH (…) clause carries driver options, so a single DSN can read many sources.
-
Rows returned to your tool
CustomerID CompanyName C1 Company 1 C3 Company 3 Connect Power BI, Excel, SSIS, Python or SQL Server to the DSN to get the same rows.
Illustrative input and output. More query samples are in the online help.
SQL query examples
Copy a query, change the source, run it.
13 of the 25 examples from the FTP / SFTP Driver for XML Files help. Open one to see the SQL.
Read files, API responses and objects as rows.
Query Single File
This example shows how to query single XML file (For multiple file use wildcard (i.e. *.xml)). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).
SELECT * FROM $ WITH (SRC='zappysys-public-bucket/cust-1.xml')
Query Multiple Files
This example shows how to query multiple XML files using wildcard (i.e. *.xml). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).
SELECT * FROM $
WITH (
SRC='zappysys-public-bucket/cust*-?.xml'
--,RECURSIVE='True' --Include files from sub folder
)
Query direct XML string (embedded inside query)
This example shows how to query direct XML string embedded inside query. This is useful for testing purpose. In real world this may not be the case because data may be coming from File or URL (API call).
SELECT * FROM [data.row]
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA='
<data>
<row><id>1</id><name>AAA</name></row>
<row><id>2</id><name>BBB</name></row>
</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
)
Query with static Metadata (META option / Override Mode)
This example shows how to supply custom Metadata rather than relying dynamic metadata detection (based on 300 rows scanning). If you run query you can click on [View Metadata] button to generate default metadata.
SELECT * FROM [data.row]
WITH
(DATA='
<data>
<row><id>1</id><checknumber>0001</checknumber><name>AAA</name></row>
<row><id>2</id><checknumber>0002</checknumber><name>BBB</name></row>
</data>'
--//Method-1: >>>DIRECT META (compact mode)<<<
,META='id:int;checknumber:string(10);name:string(10)'
-- Add: @OverrideMode:1 in the column list to override meta for only specific column(s) - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--,META='@OverrideMode:1;id:int;checknumber:string(10)'
--//Method-2: >>>DIRECT META (xml mode)<<<
--,META='[{Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10},{Name:"name",Type:"String",Length:10}]'
-- Add: @OverrideMode entry in the column list to override meta for only specific column(s). No need to set Type for that entry - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--,META='[{Name:"@OverrideMode"}, {Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10}]'
--//Method-3: >>>META from File <<<
--,META='c:/data/meta.xml'
--//Method-4: >>>META from Internal Storage - Just refer entry by name <<<
--,META='MY_META_STORAGE_NAME'
)
Escape Sequence (String Literals with new lines, tabs)
This example shows how to use escape sequence in String Literals (Quoted values (single quotes) in WITH, SELECT or some other clause if driver supports). You can use @ symbol before string literal to allow special characters (e.g. /r /n /t /v /u0041 //).
SELECT * FROM [data.row]
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA=@'<data>\r\n<row><id>1</id><name>AA\u0041</name></row>\r\n<row><id>2</id><name>BBB</name></row>\r\n</data>'
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row).
)
Read unstructured data and pass to API call
This example shows how to query unstructured data and parse using Regex, XPath or XMLPath) Examples of XPaths: Examples of XmlPath: Examples of Regular Expression: Also try following Query Examples to extract Raw data.
SELECT c.title,o.url,o.data
FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='<title>([^<]*)<\/title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, JsonPath or XPath. Use double pipe to split multiple columns
,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Json template. Comment this line to generate default columns e.g. col1, col2...If you comment this then use c.col1 and c.col2 instread of c.title and c.content in Select and join1_xxxxx attributes
,RawOutputExtractMode='Regex' --can be Regex, Xml, Json or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)
,Alias='c'
,join1_RequestMethod='GET'
,join1_src='mybucket/myservice/[$c.title$]/log.ext'
,join1_RequestContentTypeCode = 'TextPlain'
,join1_alias='o'
,join1_EnableRawOutputModeSingleRow='False'
)
Flatten Hierarchy with Filter (XMLPath / Regular Expression / Regex)
This example shows how to query data using direct Filter expression and some other filter related settings. It also shows how to use regular expressions to filter nodes.
SELECT * FROM $
WITH (
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/somefile.xml'
--SRC='zappysys-public-bucket/some*.xml'
DATA='
<data parentCol1="p1" parentCol2="p2">
<row><id>1</id><name>AAA</name></row>
<row><id>2</id><name>BBB</name></row>
</data>'
,Filter='$.data.row[*]' -- for more information check this https://zappysys.com/blog/xmlpath-examples-expression-cheetsheet/
--,Filter='$.data.row[?(@name=='AAA')]' --//equal match example
--,Filter='$..[?(@name=='AAA')]' --//equal match example (scan all levels - parent or child)
--,Filter='$.data.row[?(@name=~/AAA|BBB/)]' --//regex match example
,ElementsToTreatAsArray='row' --if you don't specify this option then your query may fail when only 1 <row> tag is found (use comma for multiple names e.g. order,item,row)
,IncludeParentColumns='true' --this will include any columns found above filtered node (e.g. rows).
,ParentColumnPrefix='p_'
)
Functions, GROUP BY, CASE, UNION, temp tables and more.
String Manipulation
This example shows how to use string functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.
SELECT
substr('ZappySys', 1, 5)
, printf('%s %s %.2f', 'v1', CompanyName, CompanyId)
, ('Str1' || CompanyName || 'Str2') as ConcatExample
, upper('ZappySys')
, lower('ZappySys')
, replace('ZappySys', 'Sys', 'XYZ')
, instr('ZappySys', 'Sys')
, trim(' Zappy Sys ') trim1
, trim('@@@ZappySys@@@', '@') trim2
, rtrim(' ZappySys ')
, ltrim(' ZappySys ')
, length( CompanyName )
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
<Rows><CompanyId>1002</CompanyId><CompanyName>Amazon</CompanyName></Rows>
</Info>
')
Control flow
This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.
SELECT
COALESCE(10,20) coalesce1 --returns first non-null arg
,COALESCE(null,20) coalesce2 --returns first non-null arg
,COALESCE(null,20,30,40) coalesce3 --returns first non-null arg
,IFNULL(null,20) --returns first non-null arg. Same function as COALESCE but simple (just two arguments allowed)
,NULLIF(20,20) nullif1 --returns null if both argument same
,NULLIF(10,20) nullif2 --returns null if both argument same
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
</Info>
')
Date/Time
This example shows how to use date/time functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation. XML driver supports following 5 datetime functions.
SELECT
DATE('now') date_now
,STRFTIME('%Y-%m-%dT%H:%M:%f','now') formatted_Date_time
,DATETIME('now') datetime_now
,DATE('now','localtime') datetime_now_local
,TIME('now') time_now
,JULIANDAY('now')
,DATE('now', 'start of month', '+1 month' , '-1 day' ) dt_modify1
,DATE('now', '+5 day') dt_modify2
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
<Rows><CompanyId>1002</CompanyId><CompanyName>Amazon</CompanyName></Rows>
</Info>
')
Math
This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.
SELECT
abs(-500)
,random()
,round(1000.236, 2)
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='
<Info>
<Rows><CompanyId>1000</CompanyId><CompanyName>ZappySys</CompanyName></Rows>
<Rows><CompanyId>1001</CompanyId><CompanyName>Microsoft</CompanyName></Rows>
</Info>
')
Pivot Value (Columns to Rows)
This example shows how to pivot column to rows (e.g. Transform property name as row value). By default Tag name (i.e. Property) outputs as Column name but when your column name is Dynamic then you can use Pivot Feature to transform the data.
SELECT
Pivot_Name as ProjectName,
Pivot_Value_id as Id,
Pivot_Value_code as Code
FROM $
WITH (
Filter='$.data',
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.xml'
--SRC='zappysys-public-bucket/some*.xml'
--SRC='zappysys-public-bucket/somefile.xml'
DATA='
<data>
<project1><id>1</id><code>AAA</code></project1>
<project2><id>2</id><code>BBB</code></project2>
<project3><id>3</id><code>CCC</code></project3>
</data>',
EnablePivot='True'
)
Group By / Limit / Order By
This example shows how to use GROUP BY / ORDER BY and LIMIT clauses.
SELECT
Country AS Invoice_Country
, SUM(UnitPrice * Quantity) AS Invoice_Total
FROM "Root.value"
WHERE Discount > 0
--AND OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR use DATE('1997-12-31') DateTime column can be queried this way. You must wrap DATETIME function around it. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)
GROUP BY Country
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY Invoice_Total DESC --,DATETIME(OrderDate)
LIMIT 3
WITH (SRC='zappysys-public-bucket/invoices.xml')
Run the same query from SQL Server or your own code.
SQL Server (linked server)
Query the driver from SQL Server through the Data Gateway. The driver does not have to be installed on SQL Server.
-- SQL Server: create a linked server to the ZappySys Data Gateway (here [ZAPPY_GATEWAY]),
-- then run the driver query through OPENQUERY.
SELECT *
FROM OPENQUERY([ZAPPY_GATEWAY], 'SELECT * FROM $
WITH (
SRC=''zappysys-public-bucket/cust*-?.xml''
--,RECURSIVE=''True'' --Include files from sub folder
)')
C# (System.Data.Odbc)
using System;
using System.Data.Odbc;
// Use a DSN ("DSN=MyZappyDsn;") or name the driver directly
using (var conn = new OdbcConnection("DRIVER={ZappySys SFTP XML Driver};"))
{
conn.Open();
var cmd = new OdbcCommand(@"SELECT * FROM $
WITH (
SRC='zappysys-public-bucket/cust*-?.xml'
--,RECURSIVE='True' --Include files from sub folder
)", conn);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
Console.Write("{0}={1} ", rdr.GetName(i), rdr[i]);
Console.WriteLine();
}
}
}
Python (pyodbc)
import pyodbc
# Use a DSN ("DSN=MyZappyDsn;") or name the driver directly
conn = pyodbc.connect("DRIVER={ZappySys SFTP XML Driver};")
cursor = conn.cursor()
cursor.execute(r"""
SELECT * FROM $
WITH (
SRC='zappysys-public-bucket/cust*-?.xml'
--,RECURSIVE='True' --Include files from sub folder
)
""")
for row in cursor.fetchall():
print(row)
conn.close()
PowerShell
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DRIVER={ZappySys SFTP XML Driver};" # or "DSN=MyZappyDsn"
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = @'
SELECT * FROM $
WITH (
SRC='zappysys-public-bucket/cust*-?.xml'
--,RECURSIVE='True' --Include files from sub folder
)
'@
$table = New-Object System.Data.DataTable
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($table) | Out-Null
$conn.Close()
$table | Format-Table
Java (JDBC through Data Gateway)
// Java uses the ZappySys Data Gateway and the Microsoft SQL Server JDBC driver.
// On the gateway, add a data source (here "MyZappySource") that uses the ZappySys SFTP XML Driver.
import java.sql.*;
String url = "jdbc:sqlserver://localhost:5000;databaseName=MyZappySource;user=MyGatewayUser;password=MyPassword;encrypt=false";
String sql = "SELECT * FROM $\n"
+ "WITH (\n"
+ " SRC='zappysys-public-bucket/cust*-?.xml'\n"
+ " --,RECURSIVE='True' --Include files from sub folder\n"
+ ")";
try (Connection conn = DriverManager.getConnection(url);
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql)) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
PHP (odbc_connect)
<?php
$conn = odbc_connect("DRIVER={ZappySys SFTP XML Driver};", "", ""); // or "DSN=MyZappyDsn"
$sql = <<<'SQL'
SELECT * FROM $
WITH (
SRC='zappysys-public-bucket/cust*-?.xml'
--,RECURSIVE='True' --Include files from sub folder
)
SQL;
$rs = odbc_exec($conn, $sql);
while ($row = odbc_fetch_array($rs)) {
print_r($row);
}
odbc_close($conn);
Articles
Guides and tutorials for the FTP / SFTP Driver for XML Files
Compatibility and requirements
Built for Windows, and for your own environment.
- Windows 11, 10, 8 and 7, and Windows Server 2022, 2019, 2016, 2012 R2 and 2008
- .NET Framework 4.x. The JDBC Bridge Driver also needs Java 8 or later
- 32-bit and 64-bit ODBC drivers. SQL Server 2012 through 2025 through the Data Gateway
- Self-hosted: drivers run in your Windows process, business data does not pass through ZappySys servers, and credentials stay in your DSN
- Free trial of ODBC PowerPack: test with your own data before you buy
FAQ
Questions about the FTP / SFTP Driver for XML Files
Which tools and languages can use this driver?
Any ODBC-compliant tool, such as Power BI, Tableau, Qlik, SSRS, Informatica, Excel and SSIS. You can also connect from Java, C#, Python, PowerShell and other languages with an ODBC connection string.
Do I need to install the driver on SQL Server?
No. With the Data Gateway option the driver runs on the gateway machine and SQL Server connects to it through a linked server.
Which edition includes the FTP / SFTP Driver for XML Files?
The FTP / SFTP Driver for XML Files is included in the Professional and Enterprise editions of ODBC PowerPack. See the edition comparison for the full list.
Can I query many files, or compressed files?
Yes. Use a wildcard in SRC, add RECURSIVE='True' for sub-folders, and set FileCompressionType to GZip or Zip.
Can I save a query to reuse it?
Yes. Save a query as a virtual table or a stored procedure (custom objects) in the DSN. Tools then see it like a normal table, and a procedure can take parameters. See custom objects in ODBC drivers.
Is there an SSIS version?
Yes. This driver uses the same data engine as the XML Source in SSIS PowerPack.
Which SQL Server and Windows versions are supported?
Support for SQL Server 2025, 2022, 2019, 2017, 2016, 2014 and 2012 (32/64 bit) through the Data Gateway. See the system requirements.
Can I try it before buying?
Yes. Download ODBC PowerPack, create a DSN and test your own data before you buy.
Start your free trial today
Install ODBC PowerPack and add a FTP / SFTP Driver for XML Files DSN.
Preview your data in the driver UI before you connect your tool.
- One DSN, many toolsPower BI, Excel, SSIS, Python and SQL Server can all use it.
- Free trialTest the driver with your own data before you buy.
- Live supportHelp during your trial and after purchase.
Open Help FileBrowse all ODBC PowerPack drivers
New purchases may be eligible for refund review within 30 days. See the refund policy.
Talk to an engineer before or during your trial.