ODBC FTP / SFTP Driver (for XML Files)

⚡ Key Benefits

• SQL on FTP/SFTP XML — Query XML files on FTP, SFTP, or FTPS servers using SELECT, WHERE, GROUP BY syntax
• Wildcard & Multi-File — Read single or multiple files using pattern matching (e.g., folder/cust*-?.xml)
• Element Flattening — Automatically flatten nested XML elements into tabular rows with ElementsToTreatAsArray
• Compressed File Streaming — Read GZip and Zip XML files directly without pre-extraction
• Centralized Access — Share driver via Data Gateway without local installs on client machines

📸 Visual Overview & Setup

1. Configure FTP / SFTP Connection 🔍 View Full Size	2. Browse FTP/SFTP XML Files 🔍 View Full Size	3. Use in Power BI 🔍 View Full Size
4. Use in Excel 🔍 View Full Size	5. Query from SQL Server (T-SQL) 🔍 View Full Size	6. Share via Data Gateway Data Gateway overview

🔗 Seamless SQL Query Integration

-- Aggregate XML invoice data from FTP/SFTP server
SELECT
    Country AS Invoice_Country,
    SUM(UnitPrice * Quantity) AS Invoice_Total
FROM $
WHERE Discount > 0
GROUP BY Country
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY Invoice_Total DESC
LIMIT 3
WITH (SRC='zappysys-public-bucket/invoices.xml')

🔧 Key Features

Read & Query XML from FTP / SFTP

• SELECT with WHERE, ORDER BY, GROUP BY, LIMIT, and HAVING on FTP/SFTP-hosted XML files
• Read single file (folder/cust-1.xml) or multiple files via wildcard (folder/cust*-?.xml)
• Embedded XML queries — pass inline XML strings with DATA='...' for testing or static lookups
• UNION ALL and temp tables (#tbl) for combining results from multiple queries

Element Flattening & Transforms

• Flatten nested XML elements into tabular rows using ElementsToTreatAsArray='row'
• Handle single-element edge cases — prevents query failures when only one child tag is found
• Pivot data — transform XML sibling elements into rows with EnablePivot='True'
• Auto-fill missing columns with null values for inconsistent XML structures

SQL Functions

• String: substr, printf, upper, lower, replace, trim, length
• Date/time: DATE, DATETIME, STRFTIME, JULIANDAY with modifiers
• Math: abs, round, random
• Control flow: COALESCE, IFNULL, NULLIF, CASE

File Handling & Compression

• Read GZip and Zip compressed XML files in streaming mode
• Wildcard pattern matching for multi-file reads across FTP directories
• Recursive sub-folder scanning with RECURSIVE='True'

Security & Platform

• FTP, SFTP (SSH), and FTPS (TLS/SSL) protocols with username/password or key-based authentication
• Standard ODBC for Power BI, Tableau, Qlik, Excel, SSRS, Informatica, Talend, SSIS, and custom apps
• Server-side access via Data Gateway — no local driver install required

💡 Common Use Cases

Typical scenarios for the FTP / SFTP ODBC Driver for XML Files in reporting, ETL, and app development:

• FTP XML reporting in Power BI or Excel: Query XML files on FTP/SFTP servers and load into BI dashboards.
  Example: SELECT * FROM $ WITH (SRC='uploads/cust-1.xml')
• Multi-file aggregation: Read and aggregate many XML files using wildcard patterns.
  Example: SELECT * FROM $ WITH (SRC='data/logs*.xml', RECURSIVE='True')
• Flatten nested XML elements: Convert repeating XML child elements into flat, queryable rows.
  Example: SELECT * FROM [data.row] WITH (SRC='folder/data.xml', ElementsToTreatAsArray='row')
• Pivot XML siblings to rows: Transform sibling XML elements into tabular data for analytics.
  Example: use EnablePivot='True' to convert <project1>, <project2> into separate rows.
• Query compressed FTP files: Read GZip or Zip XML files directly without extracting first.
  Example: the driver streams and decompresses on-the-fly.
• Server-side access without local ODBC: Route queries through Data Gateway from SQL Server Linked Server or JDBC clients.
  Example: Java or Linux services query FTP/SFTP XML data via the gateway JDBC connection.

🎯 Summary

Bring FTP/SFTP XML data into the ODBC ecosystem your team already uses.
Query with SQL, flatten nested elements, pivot sibling nodes, aggregate across multiple files, and connect from Power BI, SQL Server, Excel, or code — without writing custom file-transfer integrations.

Trusted by Developers & IT Teams Worldwide Built for ODBC Workloads: One driver model across BI, ETL, databases, and custom code. Expert Technical Support: Engineers help with connection strings, SQL queries, and FTP/SFTP configuration. Proven Enterprise Scale: Thousands of teams worldwide rely on ZappySys for API and cloud connectivity.

ODBC PowerPack Includes FTP / SFTP ODBC Driver for XML Files Read reviews on Capterra →