Export Table Contents as XML into Variable in HANA Database using SQLScript

Database developers working on SAP HANA database can export contents of a database table in XML format into a SQL variable using SQLScript. With enhancements introduced with HANA 2, HANA database SQL developers can use SELECT FOR XML to retrieve contents of a database table in XML format. In this SQLScript tutorial, I will show how HANA developer can store the extracted XML data into a string variable.

Let's assume you have a database table in your schema named City.
SQLScript developers can extract the sample table data using SELECT FOR XML simply executing following statement.


SELECT * FROM City FOR XML

Code

Here is the output produced by above SQL script

export table data as XML in SAP HANA database using SQL

By the way, the basic syntax for SELECT FOR XML in SQLScript can be summarized by following two usages on a SAP HANA database.


SELECT * FROM <table name> FOR XML



SELECT * FROM <table name> FOR XML (<formatting options>)

Code

And what are the formatting options for SELECT FOR XML command?
Basically, columnstyle and header are the formatting options with following possible values.


SELECT * FROM City FOR XML('columnstyle'='attribute', 'header'='yes')

Code

If columnstyle is not defined with "attribute" option, we have each column as XML tags in the resultant XML output. If 'columnstyle'='attribute' formatting option is used, columns are mapped to attributes in the final XML extract.

If "header" option has the value "yes", then we have the XML definition tag and related attributes within the output XML.
"<?xml version="1.0" encoding="UTF-8" ?>"

Let's now see how SQLScript developer can extract database table data into XML and store into a SQL variable.


do

begin



declare xml string;



SELECT * into "XML" FROM City FOR XML;

select :XML from dummy;



SELECT * into "XML" FROM City FOR XML( 'columnstyle'='attribute', 'header'='yes');

select :XML from dummy;



end;

Code

The first XML string variable has the following value stored for the FOR XML query without explicitly defined formatting options:

export tabular data into string variable using SQLScript in SAP HANA database

And the second "SELECT FOR XML" with additional formatting options for columnstyle as "attribute" and header as "yes", produces following XML output

export table data as XML with columns expressed as attributes using HANA SQLScript

If the database developer requires to export or extract table data by filtering it instead of exporting all table data into XML, a suitable WHERE clause should be used as follows.


declare xml string;

SELECT * into "XML" FROM City WHERE CITYID = 1 FOR XML;

select :XML from dummy;

Code