How to export SQL Server XML column into a document in Python ?

9 January 2023

Python code to read the data from a SQL Server XML column stored in a table and export it to an XML document.

Step-by-step tutorial on how to export SQL Server XML column into a document in Python. To create an XML table in SQL Server, we first define the table schema using an XML schema collection. The data from the original table is then inserted into the new XML table. Using the pyodbc library, we establish a connection to the SQL Server database and retrieve the XML data from the XML type table.

We create an XML document using the lxml module and loop through each row in the SQL Server table to create a new Element object. Finally, we write the XML data to a file using the tostring() method with the ‘pretty_print’ and ‘encoding’ options set to True and ‘utf-8’ respectively. This is a simple process that allows you to easily export XML data from SQL Server to other systems that support XML.

Step 1: Create the XML column in T-SQL

To create an XML table in SQL Server, we first need to define the table schema using an XML schema collection. Here’s an example of how to create an XML table for the “employees” data. This code creates an XML schema collection named EmployeeSchema which defines the structure of the XML data that will be stored in the employee_data column of the employees_xml table. The CREATE TABLE statement creates the employees_xml table with an id column and an employee_data column of type xml with the EmployeeSchema schema.

CREATE XML SCHEMA COLLECTION EmployeeSchema AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Employee">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ID" type="xsd:integer"/>
        <xsd:element name="Name" type="xsd:string"/>
        <xsd:element name="Department" type="xsd:string"/>
        <xsd:element name="Salary" type="xsd:integer"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>';
GO

CREATE TABLE dbo.employees_xml (
   id INT PRIMARY KEY,
   employee_data xml(EmployeeSchema)
);

The schema with an XML formatting looks like this: 

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="Employee">
		xsd:complexType
			xsd:sequence
				<xsd:element name="ID" type="xsd:integer"/>
				<xsd:element name="Name" type="xsd:string"/>
				<xsd:element name="Department" type="xsd:string"/>
				<xsd:element name="Salary" type="xsd:integer"/>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

Next, we can insert the data from the “employees” table into the new XML table. This SQL code inserts the data into the XML table. Indeed, the INSERT INTO statement inserts the data from the employees table into the employees_xml table, transforming it into an XML format using the FOR XML clause and the PATH and ROOT keywords to structure the resulting XML document.

INSERT INTO dbo.employees_xml (id, employee_data)
VALUES 
(1, '<Employee><ID>1</ID><Name>John Smith</Name><Department>Sales</Department><Salary>50000</Salary></Employee>'),
(2, '<Employee><ID>2</ID><Name>Jane Doe</Name><Department>Marketing</Department><Salary>60000</Salary></Employee>'),
(3, '<Employee><ID>3</ID><Name>Bob Johnson</Name><Department>IT</Department><Salary>70000</Salary></Employee>'),
(4, '<Employee><ID>4</ID><Name>Alice Wong</Name><Department>HR</Department><Salary>55000</Salary></Employee>');

Now we have an XML table “employees_xml” that contains the same data as the original “employees” table. We can export this table into Python using the same method as before.

Step 2: Connect to SQL Server Database

First, we need to establish a connection to the SQL Server database. We can use the pyodbc library to connect to the database using the following code:

# Set up the database connection
# declare some variables to store the connections details
driver = 'SQL Server'
server = 'localhost'
database = 'Expert-Only'

# connect to the local SQL Server database
connection = pyodbc.connect(f'DRIVER={driver};'
                            f'SERVER={server};'
                            f'DATABASE={database};'
                            f'Trusted_Connection=yes;')

Technically, in this tutoriel, we perform the exact opposite on this one on how to import an XML document in a XML typed SQL Server column.

How to import an XML document into SQL Server using Python?

Step 3: Retrieve the XML Data from the column

Next, we need to retrieve the XML data from the XML type table in SQL Server. We can use a SQL query to retrieve the data and store it in a variable as a string. 

import pyodbc

# Execute SQL query to retrieve data
cursor.execute('SELECT * FROM dbo.employees_xml')

Step 4: Convert the XML Data into an XML Document

This code block creates an XML document using the ElementTree module of the lxml library. It starts by creating the root element of the document, which is an Element object with the tag name “employees”.

The code then loops through each row in the result set of the SQL query executed using the cursor object. For each row, a new Element object is created with the tag name “employee”. This element is then added as a child to the root element of the XML document using the etree.SubElement() method.

The code then uses the etree.SubElement() method again to create child elements for each of the four columns in the SQL Server table: “id”, “name”, “department”, and “salary”. The text values for these child elements are set using the data retrieved from the SQL Server table, with the exception of the “id” element, which is set to the id value of the current row using the str() function to convert it to a string.

The “employee_data” variable contains the XML data retrieved from the “employee_data” column in the SQL Server table, which is in string format. The etree.fromstring() method is used to parse this string into an Element object, which can then be used to access the values of its child elements using the .find() method. The code sets the text values of the child elements for the “name”, “department”, and “salary” elements using the values retrieved from the parsed XML data.

# Create XML document
root = etree.Element('employees')

for row in cursor.fetchall():
    employee_data = etree.fromstring(row.employee_data)
    employee = etree.SubElement(root, 'employee')
    etree.SubElement(employee, 'id').text = str(row.id)
    etree.SubElement(employee, 'name').text = employee_data.find('Name').text
    etree.SubElement(employee, 'department').text = employee_data.find('Department').text
    etree.SubElement(employee, 'salary').text = employee_data.find('Salary').text

Step 5: Write data to the XML document

The last code block uses the etree.tostring() method to convert the root element into an XML string, which is then written to a file named “employees.xml”. The pretty_print=True argument adds line breaks and indentation to the output to make it more readable. The encoding=str argument specifies that the XML should be written as a string object rather than bytes.

The with statement is used to open the file and automatically close it after the write operation is complete. The open() function takes two arguments: the file path/name and the mode in which to open the file. In this case, we use the mode w to open the file for writing.

# Save XML document to file
tree = ET.ElementTree(root)
tree.write('employees.xml', encoding='utf-8', xml_declaration=True)

Step 6: Full Python code to wrap up all the steps

The complete code for exporting an XML type table from SQL Server into an XML document in Python is shown below. To export data from a SQL Server XML table to an XML document in Python, we need to perform the following steps, listed here in detail:

  • Import the required libraries, i.e., pyodbc and lxml.etree.
  • Declare variables to store the connection details for the database.
  • Establish a connection to the SQL Server database using the pyodbc library.
  • Create a cursor object to execute SQL queries.
  • Execute an SQL query to retrieve data from the ’employees_xml’ table.
  • Create an XML document using the ElementTree module and assign the root element the tag name ’employees’.
  • Loop through each row in the queried SQL Server table, create a new ’employee’ Element object, and set child elements for each of the four columns with the corresponding data.
  • Convert the XML document to a string and save it to a file with the file path ‘C:\data\employees.xml’.
  • Close the database connection.

This is a straightforward process that allows you to easily incorporate XML data into your Python projects or export it to other systems that require XML data.

import pyodbc
import lxml.etree as etree

# Set up the database connection
# declare some variables to store the connections details
driver = 'SQL Server'
server = 'localhost'
database = 'Expert-Only'

# connect to the local SQL Server database
connection = pyodbc.connect(f'DRIVER={driver};'
                            f'SERVER={server};'
                            f'DATABASE={database};'
                            f'Trusted_Connection=yes;')
cursor = connection.cursor()

# Execute SQL query to retrieve data
cursor.execute('SELECT * FROM dbo.employees_xml')

# Create XML document
root = etree.Element('employees')

for row in cursor.fetchall():
    employee_data = etree.fromstring(row.employee_data)
    employee = etree.SubElement(root, 'employee')
    etree.SubElement(employee, 'id').text = str(row.id)
    etree.SubElement(employee, 'name').text = employee_data.find('Name').text
    etree.SubElement(employee, 'department').text = employee_data.find('Department').text
    etree.SubElement(employee, 'salary').text = employee_data.find('Salary').text

# Save XML document to file
xml_string = etree.tostring(root, pretty_print=True, encoding=str)
with open('C:\data\employees.xml', 'w') as f:
    f.write(xml_string)

# Close database connection
cursor.close()
connection.close()

The XML document exported looks like, thanks to the indentation option used:

<employees>
  <employee>
    <id>1</id>
    <name>John Smith</name>
    <department>Sales</department>
    <salary>50000</salary>
  </employee>
  <employee>
    <id>2</id>
    <name>Jane Doe</name>
    <department>Marketing</department>
    <salary>60000</salary>
  </employee>
  <employee>
    <id>3</id>
    <name>Bob Johnson</name>
    <department>IT</department>
    <salary>70000</salary>
  </employee>
  <employee>
    <id>4</id>
    <name>Alice Wong</name>
    <department>HR</department>
    <salary>55000</salary>
  </employee>
</employees>

Conclusion on exporting XML data to document with Python

In this Python tutorial, we have seen how to export a SQL Server XML typed column, with a basic schema, into an XML document using Python. We used the pyodbc library to connect to the SQL Server database and retrieve the XML data, and the lxml library to parse the XML data and write it to the XML file.

Indeed, Python provides easy and efficients ways to work with XML data, so we can easily integrate our Python code with a SQL Server database and work with XML data. I really hope this article was helpful in understanding how to export XML documents directly from SQL Server columns with Python.

If you have any questions or comments, feel free to leave them below as, we’ll come back to you as soon as possible. To go further, let’s learn how to export a MS SQL table into a CSV file using Python.

How to export a SQL Server table into a CSV file in Python?

Be the first to comment

Leave a Reply

Your email address will not be published.


*