How To Export Data From Database Query To XML
Tags: database, xml, postgres, sqlserver
Published: 2021-06-30 02:55:51

## Introduction

[Extensible Markup Language (XML)](https://en.wikipedia.org/wiki/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:

```xml
<!-- 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](https://www.w3.org/TR/REC-xml/#sec-well-formed).

## 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:

```xml
<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.

```sql
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.

```xml
<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](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f386322015711406bed8038dff284003)

## 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.
1. `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.
1. `tableforest` - Will put each row in different XML documents. That means that each query data row will be wrapped in a **row** root element.
1. `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:

```sql
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:

```xml
<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](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=16909c7c0600b8966b471dccd52b2dfd)

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](https://wiki.postgresql.org/wiki/COPY)** function, you can copy the  XML output directly to a file using the following syntax:

```sql
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](https://www.postgresql.org/docs/current/sql-copy.html)** 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.

```sql
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:

```xml
<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](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e3ff65a7e577e6d987be34ee5b74d962)

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**)](https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15). 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.

```sh
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](https://www.postgresql.org/docs/current/functions-xml.html)
- [FOR XML (SQL Server)](https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-ver15)