SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




Install SAP Free


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

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>)

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')

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;

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;





SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems