XML Connector for Python

In this article you will learn how to integrate XML data in Python (live / bi-directional connection to XML). XML Connector can be used to extract and output XML data coming from REST API web service calls (Web URL) or direct XML String (variables or DB columns) or local XML files data. XML Connector also supports Path expression to extract data from any level. This Connector is optimized to work with very large XML string..

Using XML Connector you will be able to connect, read, and write data from within Python. Follow the steps below to see how we would accomplish that.

Download Documentation

Create ODBC Data Source (DSN) based on ZappySys XML Driver

To get data from XML using Python we first need to create a DSN (Data Source) which will access data from XML. We will later be able to read data using Python. Perform these steps:

  1. Install ZappySys ODBC PowerPack.

  2. Open ODBC Data Sources (x64):
    Open ODBC Data Source

  3. Create a User Data Source (User DSN) based on ZappySys XML Driver

    ZappySys XML Driver
    Create new User DSN for ZappySys XML Driver
    You should create a System DSN (instead of a User DSN) if the client application is launched under a Windows System Account, e.g. as a Windows Service. If the client application is 32-bit (x86) running with a System DSN, use ODBC Data Sources (32-bit) instead of the 64-bit version.
  4. Select Url or File and paste the following Url for this example OR you can load existing connection string as per this article.

    NOTE: Here for demo, We are using odata API, but you need to refer your own API documentation and based on that you need to use your own API URL and need to configure connection based on API Authentication type

  5. Now enter Path expression in Array Filter textbox to extract only specific part of XML file as below ($.feed.entry[*] will get content of entry attribute from XML document. Entry attribute is array of XML documents so we have to use [*] to indicate we want all records of that array)

    NOTE: Here, We are using our desired filter, but you need to select your desired filter based on your requirement.


    Click on Test Connection button to view whether the Test Connection is SUCCESSFUL or Not.

    $.feed.entry[*]
    ZappySys ODBC Driver - Configure XML Driver
  6. Once you configured a data source, you can preview data. Hit Preview tab, and use similar settings to preview data:
    ZappySys ODBC Driver - Preview XML Driver

  7. Click OK to finish creating the data source.

  8. That's it; we are done. In a few clicks we configured the call to XML API using ZappySys XML Connector.

Read data in Python from the DSN

  1. Python code to get the data:

    XmlDSN')
    Python code to get the data from ZappySys DSN

  2. When you run the code it will make the API call and read the data:
    Python - Extracted data from ZappySys DSN

  3. Here is Python program's code in text format:

    
        import pyodbc
        conn = pyodbc.connect('DSN=XmlDSN')
    
        cursor = conn.cursor()
    
        #execute query to fetch data from API service
        cursor.execute("SELECT id,title FROM products")
    
        row = cursor.fetchone()
        while row:
            print(row)
            row = cursor.fetchone()
    
        ##For loop example
        #for row in cursor:
        #    print(row)
    

  4. If you want to avoid to be dependent on a DSN name and creating multiple DSNs for each platform (x86, x64), then you can use a fully qualified connection string. Simply go to your DSN and copy the Connection String . Then in your Python code use Connection String when initializing OdbcConnection object, for example:

    conn = pyodbc.connect('DRIVER={ZappySys XML Driver};ServiceUrl=https://yourservices.provider.com/api/xxxx....;AuthName=Http;')
    How to get ZappySys Driver Connection String?

    Please follow the instructions below to retrieve the connection string for the ZappySys driver.

    1. Click on the Windows Start menu.

    2. In the search bar, type ODBC and press Enter.

    3. From the search results, choose ODBC Data Sources or ODBC Data Sources (32-bit) or a similar option depending on your system architecture and ODBC driver configuration.

    4. Choose your data source from the list, then click on the Configure button.
    5. After opening the Data Source UI, you should copy the connection string to a Notepad or text file for reference.
    6. Click on Copy Connection String button.
      When you click 'Copy Connection String,' you may encounter the following option:
      Choose the third option All Settings to copy everything and click on OK button.
      zappysys-data-source-copy-connectionstring
      zappysys-data-source-copy-connectionstring

    That's it connection string has been successfully copied.

How to install `pyodbc` in the Python?

You would need to install pyodbc in Python if you intend to establish connections to databases that support ODBC (Open Database Connectivity). This module facilitates communication between Python applications and various database management systems, enabling you to perform operations such as querying, retrieving data, and managing databases. Here's how you can install pyodbc in Python:

Installation Steps:

  1. Ensure you have Python installed on your system. If not, download it from the official Python website and follow the installation instructions.

  2. Open your terminal or command prompt.

  3. Use the following command to install pyodbc using pip, the Python package installer:

    python -m pip install "pyodbc"

    Make sure you have a stable internet connection and the necessary permissions to install Python packages.


    Python - pip install pyodbc

Reasons to Install:

  • If pyodbc is not installed, your Python script will generate the following error:
    "ModuleNotFoundError: No module named 'pyodbc'"
    .
  • Database Connectivity: pyodbc allows Python to connect to various databases that support ODBC, such as Microsoft SQL Server, PostgreSQL, MySQL, and more.

  • Data Operations: It facilitates the execution of SQL queries, retrieval of data, and other database operations from within Python scripts.

  • Cross-Platform Support: pyodbc is designed to work across different operating systems, including Windows, macOS, and various Linux distributions.

  • Simplicity and Efficiency: The module provides an intuitive interface for managing database transactions and connections, simplifying the process of working with databases in Python.

By installing pyodbc, you can seamlessly integrate your Python applications with a wide range of ODBC-supported databases, enabling efficient and effective data management and analysis.

Configuring pagination in the XML Driver

ZappySys XML Driver equips users with powerful tools for seamless data extraction and management from REST APIs, leveraging advanced pagination methods for enhanced efficiency. These options are designed to handle various types of pagination structures commonly used in APIs. Below are the detailed descriptions of these options:

  1. Page-based Pagination: This method works by retrieving data in fixed-size pages from the Rest API. It allows you to specify the page size and navigate through the results by requesting different page numbers, ensuring that you can access all the data in a structured manner.

  2. Offset-based Pagination: With this approach, you can extract data by specifying the starting point or offset from which to begin retrieving data. It allows you to define the number of records to skip and fetch subsequent data accordingly, providing precise control over the data extraction process.

  3. Cursor-based Pagination: This technique involves using a cursor or a marker that points to a specific position in the dataset. It enables you to retrieve data starting from the position indicated by the cursor and proceed to subsequent segments, ensuring that you capture all the relevant information without missing any records.

  4. Token-based Pagination: In this method, a token serves as a unique identifier for a specific data segment. It allows you to access the next set of data by using the token provided in the response from the previous request. This ensures that you can systematically retrieve all the data segments without duplication or omission.

Utilizing these comprehensive pagination features in the ZappySys XML Driver facilitates efficient data management and extraction from REST APIs, optimizing the integration and analysis of extensive datasets.

For more detailed steps, please refer to this link: How to do REST API Pagination in SSIS / ODBC Drivers

ZappySys Authentication

ZappySys offers various authentication methods to securely access data from various sources. These authentication methods include OAuth, Basic Authentication, Token-based Authentication, and more, allowing users to connect to a wide range of data sources securely.

ZappySys Authentication is a robust system that facilitates secure access to data from a diverse range of sources. It includes a variety of authentication methods tailored to meet the specific requirements of different data platforms and services. These authentication methods may involve:

  1. OAuth: ZappySys supports OAuth for authentication, which allows users to grant limited access to their data without revealing their credentials. It's commonly used for applications that require access to user account information.

  2. Basic Authentication: This method involves sending a username and password with every request. ZappySys allows users to securely access data using this traditional authentication approach.

  3. Token-based Authentication: ZappySys enables users to utilize tokens for authentication. This method involves exchanging a unique token with each request to authenticate the user's identity without revealing sensitive information.

By implementing these authentication methods, ZappySys ensures the secure and reliable retrieval of data from various sources, providing users with the necessary tools to access and integrate data securely and efficiently. For more comprehensive details on the authentication process, please refer to the official ZappySys documentation or reach out to their support team for further assistance.

For more details, please refer to this link: ZappySys Connections

Create Custom Stored Procedure in ZappySys Driver

You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.

Steps to create Custom Stored Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

  1. Go to Custom Objects Tab and Click on Add button and Select Add Procedure:
    ZappySys Driver - Add Stored Procedure

  2. Enter the desired Procedure name and click on OK:
    ZappySys Driver - Add Stored Procedure Name

  3. Select the created Stored Procedure and write the your desired stored procedure and Save it and it will create the custom stored procedure in the ZappySys Driver:
    Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

    CREATE PROCEDURE [usp_get_orders]
        @fromdate = '<<yyyy-MM-dd,FUN_TODAY>>'
     AS
        SELECT * FROM Orders where OrderDate >= '<@fromdate>';
    

    ZappySys Driver - Create Custom Stored Procedure

  4. That's it now go to Preview Tab and Execute your Stored Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:

    Exec usp_get_orders '1996-01-01';

    ZappySys Driver - Execute Custom Stored Procedure

  5. Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
    As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.

    SELECT * FROM OPENQUERY([MY_API_SERVICE], 'EXEC usp_get_orders @fromdate=''1996-07-30''')

    ZappySys Driver - Generate SQL Server Query

  6. Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
    ZappySys Driver - Generate SQL Server Query

Create Custom Virtual Table in ZappySys Driver

ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.

If you're dealing with Microsoft Access and need to import data from an SQL query, it's important to note that Access doesn't allow direct import of SQL queries. Instead, you can create custom objects (Virtual Tables) to handle the import process.

Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.

  1. Go to Custom Objects Tab and Click on Add button and Select Add Table:
    ZappySys Driver - Add Table

  2. Enter the desired Table name and click on OK:
    ZappySys Driver - Add Table Name

  3. And it will open the New Query Window Click on Cancel to close that window and go to Custom Objects Tab.

  4. Select the created table, Select Text Type AS SQL and write the your desired SQL Query and Save it and it will create the custom table in the ZappySys Driver:
    Here is an example SQL query for ZappySys Driver. You can insert Placeholders also. Read more about placeholders here

    SELECT
      "ShipCountry",
      "OrderID",
      "CustomerID",
      "EmployeeID",
      "OrderDate",
      "RequiredDate",
      "ShippedDate",
      "ShipVia",
      "Freight",
      "ShipName",
      "ShipAddress",
      "ShipCity",
      "ShipRegion",
      "ShipPostalCode"
    FROM "Orders"
    Where "ShipCountry"='USA'

    ZappySys Driver - Create Custom Table

  5. That's it now go to Preview Tab and Execute your custom virtual table query. In this example it will extract the orders for the USA Shipping Country only:

    SELECT * FROM "vt__usa_orders_only"

    ZappySys Driver - Execute Custom Virtual Table Query

  6. Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
    As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.

    SELECT * FROM OPENQUERY([MY_API_SERVICE], 'EXEC [usp_get_orders] ''1996-01-01''')

    ZappySys Driver - Generate SQL Server Query

  7. Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
    ZappySys Driver - Generate SQL Server Query

Conclusion

In this article we discussed how to connect to XML in Python and integrate data without any coding. Click here to Download XML Connector for Python and try yourself see how easy it is. If you still have any question(s) then ask here or simply click on live chat icon below and ask our expert (see bottom-right corner of this page).

Download XML Connector for Python Documentation 


Other App Integration scenarios for XML

Other Connectors for Python


Download XML Connector for Python Documentation

  • How to connect XML in Python?

  • How to get XML data in Python?

  • How to read XML data in Python?

  • How to load XML data in Python?

  • How to import XML data in Python?

  • How to pull XML data in Python?

  • How to push data to XML in Python?

  • How to write data to XML in Python?

  • How to POST data to XML in Python?

  • Call XML API in Python

  • Consume XML API in Python

  • XML Python Automate

  • XML Python Integration

  • Integration XML in Python

  • Consume real-time XML data in Python

  • Consume real-time XML API data in Python

  • XML ODBC Driver | ODBC Driver for XML | ODBC XML Driver | SSIS XML Source | SSIS XML Destination

  • Connect XML in Python

  • Load XML in Python

  • Load XML data in Python

  • Read XML data in Python

  • XML API Call in Python