How To Export Data From Database Query To XML

Introduction

Extensible Markup Language (XML) has been in existence for more than two decades. XML is a markup language for encoding documents in a format that is both human and machine readable. In XML, you define your own custom tags, elements and attributes to meet your specific needs. XML is case sensitive, allows comments and hierarchy is important. An XML file is a text file and can be opened with any text editor. The following is a sample of an XML document:

<!-- sample comment -->
<Customers> <!-- root element -->
  <Customer>  <!-- nested element -->
    <FirstName>Helena</FirstName>
  </Customer>
  <Customer>
  ...
  </Customer>
  ...
</Customers>

When we run a query against a relational database table, the resulting data is presented mostly in tabular format. Each horizontal row represents a record while the columns flow vertically and represent an attribute of the record.

In this post, I explore how MS SQL Server and PostgreSQL databases may be queried to output the data in an XML format instead of tabular format. Each of these databases contain inbuilt functionality to output XML. You can also manually write queries combining XML elements and data from the databases to create XML output. Manually created XML output is tedious, will be missing a root element and will therefore not conform to a well formed xml document.

Create XML Manually

The sample data used within this post will be from a hypothetical customers table with the following structure and sample data.

id	first_name	last_name	city	state
1	François	Tremblay	Montreal	QC
2	Bjørn	Hansen	Oslo
3	Helena	Holý	Prague
4	Fernanda	Ramos	Brasília	DF

Create a file named customers.xml and open it with a text editor of your choice.

Create a root element in the file so that the XML file we create will be a well formed xml document by inserting the following contents to the file you:

<Customers>

</Customers>

Compose your query for data by enclosing your data columns within XML elements. Use the CONCAT() function to combine the XML elements and the data. CONCAT() function is supported by both MS SQL Server and PostgreSQL. Use COALESCE() function to replace any null values with an empty string.

SELECT CONCAT(
   '<Customer>',
      '<Id>', id, '</Id>',
      '<FirstName>', first_name, '</FirstName>',
      '<LastName>', last_name, '</LastName>',
      '<City>', COALESCE(city, ''), '</City>',
      '<State>', COALESCE(state, ''), '</State>',
   '</Customer>') customers_xml
 FROM customers LIMIT 2;

Run the query, select the results, copy and paste them between the opening and closing root element in the XML file we created.

<Customers>
  <Customer><Id>1</Id><FirstName>François</FirstName><LastName>Tremblay</LastName><City>Montreal</City><State>QC</State></Customer>
  <Customer><Id>2</Id><FirstName>Bjørn</FirstName><LastName>Hansen</LastName><City>Oslo</City><State></State></Customer>
</Customers>

Save your XML file.

SQL Fiddle

Output XML Using Database Inbuilt Functions

PostgreSQL

PostgreSQL has query_to_xml(query text, nulls boolean, tableforest boolean, targetns text) function for displaying data in XML format. The query_to_xml function requires four (4) arguments:

1. query - The actual SQL query as text.
2. nulls - If elements with null values should be included. When the value is false, columns with null values will be omitted from the XML document when the XML elements are generated. A true value for the parameter will output a self-closing element containing <columnname xsi:nil="true"/> attribute.
3. tableforest - Will put each row in different XML documents. That means that each query data row will be wrapped in a row root element.
4. targetns - The namespace to put the result in. Pass a blank string to use the default namespace.

After running the query, table tag will appear as the root element while row tag will be set for each row in the data. You can then update the table and row tags with your required tag names.

Using our sample table, we generate XML using the following:

SELECT query_to_xml(
    'SELECT t.id "Id"
     , t.first_name "FirstName"
     , t.last_name "LastName"
     , t.city "City"
     , t.state "State"
    FROM customers t LIMIT 2', true , false, '')

The output after running the function will be as follows:

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <Id>1</Id>
    <FirstName>François</FirstName>
    <LastName>Tremblay</LastName>
    <City>Montreal</City>
    <State>QC</State>
  </row>
  <row>
    <Id>2</Id>
    <FirstName>Bjørn</FirstName>
    <LastName>Hansen</LastName>
    <City>Oslo</City>
    <State xsi:nil="true"/>
  </row>
</table>

SQL Fiddle

Select and copy the XML output.

Create a file named customers.xml and open the file with a text editor.

Paste the contents you copied above to the file, replace table with Customers, row with Customer and save the file.

If you want your XML document to include the XML Schema, then use query_to_xml_and_xmlschema function instead of query_to_xml function.

Using the COPY function, you can copy the XML output directly to a file using the following syntax:

COPY(
  SELECT query_to_xml(
      'SELECT t.id "Id"
      , t.first_name "FirstName"
      , t.last_name "LastName"
      , t.city "City"
      , t.state "State"
      FROM customers t', true , false, '')
) TO '~/tmp/customers.xml'

Note that the COPY command has limited file access and user permissions. To run the command, the user running the command must be either a superuser or a member of the pg_write_server_files. However, great care should be taken when granting a user any of these roles as these roles are able to access any file on the server file system.

SQL Server

SQL Server uses a FOR XML clause in select query to transform the query results to XML output.

SELECT TOP 2
  id Id
  ,first_name FirstName
  ,last_name LastName
  ,city City
  ,COALESCE(state, '') State
FROM customers
FOR XML PATH('Customer'), ROOT('Customers')

Remember to enclose any nullable columns within the COALESCE() function to prevent them being omitted from the XML output in case they don't have values.

SQL Server has the advantage of allowing for customization of the root element and object node elements depending on FOR XML mode selected. The following will be the output after running the above query:

<Customers>
  <Customer>
    <Id>1</Id>
    <FirstName>François</FirstName>
    <LastName>Tremblay</LastName>
    <City>Montreal</City>
    <State>QC</State>
  </Customer>
  <Customer>
    <Id>2</Id>
    <FirstName>Bjørn</FirstName>
    <LastName>Hansen</LastName>
    <City>Oslo</City>
    <State></State>
  </Customer>
</Customers>

SQL Fiddle

Select and copy the XML output.

Create a file named customers.xml and open the file with a text editor.

Paste the contents you copied above to the file and save the file.

You can also create the xml file in a single step by using the bulk copy program utility (bcp). The bcp utility can be used for importing and exporting data from/to a data file.

The following command uses bcp utility to create an XML file from the sample data we have been using.

bcp "SELECT TOP 2
  id Id
  ,first_name FirstName
  ,last_name LastName
  ,city City
  ,COALESCE(state, '') State
FROM customers
FOR XML PATH('Customer'), ROOT('Customers')" queryout ~/Documents/customers.xml -S localhost -d testdb  -c -U sa

• -S specifies the SQL Server instance to connect to
• -d species the database to connect to
• -c performs the operation using a character data type without prompting for each field
• -U specifies the login ID used to connect to SQL Server

Summary

In this post, we looked at various options available to create XML data from PostgreSQl and MS SQL Server database queries. One option would be to generate the XML data manually while another option would be to use in-built functions contained within these databases. It is also possible to output XML data files using PostgreSQL COPY command or SQL Server bcp utility.

Resources

• XML Functions
• FOR XML (SQL Server)