Skip to Content

New capabilities in SAP HANA are introduced with every SP. One such important one is the ability to process XML data which has been introduced with SAP HANA 2.0 SPS1 and SPS2.

XML is in common use for interchange of data over the Internet. Thus, applications require data in xml format for communication with other entities. With the data stored in the RDMS, as SAP HANA, in relational mode as tables, the application layer requires to process the data and render in the format which could be used for communication. Similarly, when the data reaches the application layer, it needs to again process this data, but now to be able to store in the database as tables (format understood by the RDBMS).

To simplify and optimize this process, SAP HANA provides different XML functions to process this data and render it to the application layer without additional logic required, saving time and complexity at the application side.

FOR XML

<for_xml> ::= FOR XML [ ( <option_string_list> ) ] [ <returns_clause> ]

This clause, introduced in SAP HANA 2.0 SPS 01, is used to render the data from database in XML format. The database objects could be objects as column tables/row tables, virtual tables, multi store tables, views, calculation views, hierarchy views, functions, etc. It could be used as is shown below:

For example, let us create a table with the following metadata:

It is a partitioned table with different data types and column properties.

CREATE COLUMN TABLE "ALLTYPES_COL" ("ID" SMALLINT CS_INT GENERATED BY DEFAULT AS IDENTITY (start with 0 increment by -1 maxvalue 0),
	 "COUNTRY" VARCHAR (30),
	 "DATEJOINED" DATE CS_DAYDATE DEFAULT CURRENT_DATE,
	 "TIMEJOINED" TIME CS_SECONDTIME DEFAULT CURRENT_TIME,
	 "COMPLETEDATE" SECONDDATE CS_SECONDDATE DEFAULT CURRENT_UTCDATE,
	 "ENTRYTIME" LONGDATE CS_LONGDATE DEFAULT CURRENT_UTCTIMESTAMP,
	 "TINYINT_UNITS" TINYINT CS_INT DEFAULT 255,
	 "SMALLINT_VAL" SMALLINT CS_INT DEFAULT -32767,
	 "INT_VAL" INTEGER CS_INT DEFAULT -2147483648,
	 "BIGINT_VAL" BIGINT CS_FIXED DEFAULT -9223372036854775808,
	 "DEC_VAL" DECIMAL(38,
	38) CS_FIXED DEFAULT 0.1) UNLOAD PRIORITY 5 AUTO MERGE 
;
ALTER TABLE "ALLTYPES_COL" ADD ("SMALLDECIMAL_VAL" SMALLDECIMAL CS_SDFLOAT GENERATED ALWAYS AS ( 1.12 + 12.9 ))
;
ALTER TABLE "ALLTYPES_COL" ADD ("REAL_VAL" REAL CS_FLOAT DEFAULT 9223372036854.9223372036854)
;
ALTER TABLE "ALLTYPES_COL" ADD ("DOUBLE_VAL" DOUBLE CS_DOUBLE DEFAULT 9223372036854.9223372036854)
;
ALTER TABLE "ALLTYPES_COL" ADD ("FLOAT_VAL_DEF" DOUBLE CS_DOUBLE DEFAULT 9223372036854.9223372036854)
;
ALTER TABLE "ALLTYPES_COL" ADD ("FLOAT_VAL_64" DOUBLE CS_DOUBLE DEFAULT 9223372036854.9223372036854)
;
ALTER TABLE "ALLTYPES_COL" ADD ("FLOAT_VAL_32" REAL CS_FLOAT DEFAULT 9223372036854.9223372036854)
;
ALTER TABLE "ALLTYPES_COL" ADD ("BOOLEAN_VAL" BOOLEAN CS_INT DEFAULT TRUE)
;
ALTER TABLE "ALLTYPES_COL" ADD ("DESCRIPTION" NVARCHAR(40) DEFAULT 'テスト')
;
ALTER TABLE "ALLTYPES_COL" ADD ("ALPHANUM_VAL" ALPHANUM(10) CS_ALPHANUM DEFAULT '10')
;
ALTER TABLE "ALLTYPES_COL" ADD PRIMARY KEY INVERTED VALUE ("ID",
	 "DATEJOINED")
;
ALTER TABLE "ALLTYPES_COL" WITH PARAMETERS ('PARTITION_SPEC' = 'HASH 4 DATEJOINED');

Insert into the table with the following SQLs:

 

insert into "ALLTYPES_COL"(country) values ('India');
insert into "ALLTYPES_COL"(country) values ('Germany');
insert into "ALLTYPES_COL"(country) values ('USA');

 

Querying the table gives data as:

In order to render the data in xml format, we need to query it with for xml clause as below:

It has an optional set of attributes to customize the output XML. Below an example of the options provided is presented

  • Nullstyle

To omit or include, as attributes, null values in the records

Insert into the table above null value as:

 

insert into "ALLTYPES_COL"(country) values (null);

 

By Default, the null values shall be omitted as: (In the image there is no COUNTRY tag for ID -3)

 

select * from "ALLTYPES_COL" for xml;

The option NullStyle shall help to render all the values, including them when there are null values as well: (In the image, COUNTRY tag with null attribute is listed for ID -3)

select * from "ALLTYPES_COL" for xml ('nullstyle'='attribute');

 

There are multiple options such as columnstyle, format, header, incremental, nullstyle, root, rowname, schemaloc, tablename and targetns.

Additionally, returns_value helps the user to customize the output expected as: VARCHAR(n), NVARCHAR (n), CLOB, NCLOB (where n is an integer).

Complete details is available in the SAP HANA help documentation for SQL at: SAP HANA SQL and Systems Views Reference

 

XMLTABLE

XMLTABLE (

[ <XML_namespace_clause>,]

<row_pattern> PASSING <XML_argument>

COLUMNS <column_definitions>

<error_option>

);

This function, introduced in SAP HANA 2.0 SPS 02, is used to extract information from XML document and create a relational table. The XML value is provided as an argument to the XMLTABLE function along with the hierarchy to be parsed and the values to be extracted. It could be used as below:

Consider the XML below.

<resultset>

                <row>

                                <ID>0</ID>

                                <COUNTRY>India</COUNTRY>

                                <DATEJOINED>2017-08-11</DATEJOINED>

                                <TIMEJOINED>14:24:35</TIMEJOINED>

                                <COMPLETEDATE>2017-08-11 12:24:35</COMPLETEDATE>

                </row>

                <row>

                                <ID>1</ID>

                                <COUNTRY>Germany</COUNTRY>

                                <DATEJOINED>2017-08-11</DATEJOINED>

                                <TIMEJOINED>14:24:35</TIMEJOINED>

                                <COMPLETEDATE>2017-08-11 12:24:35</COMPLETEDATE>

                </row>

</resultset>

 

To store the data in the database, we usually require to parse this XML (SAX Parser) in the application layer. With XMLTABLE it makes it very easy to execute a SQL Query to the function with the XML value and the data that should be inserted in the database.

For example, we need ID, COUNTRY and COMLETEDATE from the XML value above. The Query below helps us achieve the same:

SELECT * FROM
XMLTABLE('resultset/row' PASSING
'<resultset>
 	<row>
  		<ID>0</ID>
 		<COUNTRY>India</COUNTRY>
  		<DATEJOINED>2017-08-11</DATEJOINED>
  		<TIMEJOINED>14:24:35</TIMEJOINED>
 		<COMPLETEDATE>2017-08-11 12:24:35</COMPLETEDATE>
 	</row>
 	<row>
  		<ID>1</ID>
  		<COUNTRY>Germany</COUNTRY>
  		<DATEJOINED>2017-08-11</DATEJOINED>
  		<TIMEJOINED>14:24:35</TIMEJOINED>
  		<COMPLETEDATE>2017-08-11 12:24:35</COMPLETEDATE>
 	</row>
</resultset>'
COLUMNS 
ID INT PATH 'ID', 
COUNTRY VARCHAR(200) PATH 'COUNTRY',
COMPLETEDATE VARCHAR(30) PATH 'COMPLETEDATE'
) as XTABLE

 

The result shall be obtained as:

There could be cases when the xml data is residing in a column as large data. It could be passed to the function to convert into a relational table as:

create column table CONTENT (
	id integer, 
	data nvarchar (5000)
);

insert into CONTENT values (1, '<resultset>
 	<row>
  		<ID>0</ID>
 		<COUNTRY>India</COUNTRY>
  		<DATEJOINED>2017-08-11</DATEJOINED>
  		<TIMEJOINED>14:24:35</TIMEJOINED>
 		<COMPLETEDATE>2017-08-11 12:24:35</COMPLETEDATE>
 	</row>
 	<row>
  		<ID>1</ID>
  		<COUNTRY>Germany</COUNTRY>
  		<DATEJOINED>2017-08-11</DATEJOINED>
  		<TIMEJOINED>14:24:35</TIMEJOINED>
  		<COMPLETEDATE>2017-08-11 12:24:35</COMPLETEDATE>
 	</row>
</resultset>');

SELECT * FROM
XMLTABLE('resultset/row' PASSING
CONTENT.DATA
COLUMNS 
ID INT PATH 'ID', 
COUNTRY VARCHAR(200) PATH 'COUNTRY',
COMPLETEDATE VARCHAR(30) PATH 'COMPLETEDATE'
) as XTABLE

The output from the select query is:

 

Thus, there could be any source to the function as a column/row table, virtual table, extended table, multi store table, view, etc. Similarly, since the output is a tabular result set, it could be used for any operation allowed on a table as join, view creation, in a function/procedure, etc.

 

Also, the XML could have namespace, with deep hierarchy, with attributes, etc.  Any valid xml value could be used for parsing and converting into the table. In case, there are any errors while parsing, may be due to hierarchy specification, output value type and length, ERROR OPTION could be used to specify the error handling.

The details are present and could be easily taken from well-documented function reference at: SAP HANA SQL and Systems Views Reference

 

With the two simple yet effective XML operations, it makes the life of a developer easy to deal with XML and push its handling onto the database without writing and maintaining explicit parsers and converters at the application layer.

 

It made my life very easy. Hope it shall do yours too. Kindly share your feedback if you find it helpful in your implementation and please raise questions if there are any.

 

Thank you.

Best Regards,

Anjali.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Liezl Nel

    Anjali,

    Awesome Blog, thank you for sharing! It would be really cool if this was made available via a patch in HANA 1.0 SP12 as this is EXACTLY what we need right now and we’re not upgrading to HANA 2.0 before our deadline…

    The only options we have right now is to:

    1. Manually generate the XML in a Stored Procedure!
    2. To expose it via an ODATA service as XML, but from here not sure how to write it back into a table.
    3. Introduce a parser or javascript, not prefered as it should be DB to DB
    4. We luckily need the XML for a Non-SAP application running on MSSQL and we have proven that (although a long way around) we can also write our data from our HANA Enterprise out to a MSSQL table via SDA, (readwrite remote source), then have a MSSQL Procedure pick up those fields and convert it with the “for xml” statement (as already available on MSSQL) and then write the whole thing as a complete string back into a staging table from where the particular app consumes it…

    Too Much to do when the actual thing you need is two words “FOR XML”

    Tx again for sharing!

    Liezl

    (1) 
    1. Anjali Chimnani Post author

      Hi Liezl,

       

      Certainly. It is a very desired feature for XML processing reducing time and complexity.

      But, unfortunately, it is introduced only in HANA 2 SPS01/02 since HANA 1 SPS12 is in Maintenance Revision.

      While we could certainly achieve the same functionality by writing a Scalar/Table UDF and parsing through the data set. There will be a bit lesser performance than direct “FOR XML” clause but it will enable the functionality to be done in HANA itself.

      I would be happy for any support required for UDF creation (in case required).

       

      Thank you.

      Best Regards,

      Anjali.

      (0) 
  2. Benedict Venmani Felix

    Hello Anjali,

    Well presented blog. Thank You. unfortunately we are working with XML in SPS12 and I wish we had the XML parser. I am getting an XML input from a web service and using custom xsjs code to parse the XML.

    (0) 
    1. Anjali Chimnani Post author

      Hi Benedict,

       

      Yeah till HANA 1 SPS12, it is SAX Parser APIs which help in achieving the functionality.

      While, in case, you plan to upgrade for many other capabilities in SAP HANA introduced for HANA 2, this could certainly help some refactoring.

       

      Thank you.

      Best Regards,

      Anjali.

      (0) 
  3. Mert Karakilic

    Thanks, exactly what I was looking for today. We have lots of XML files with varying contents and we would like to run some text analysis without worrying about what is in them, be able to parse the nodes and the data. Do you think feature can help? Do we have to know specific nodes in the XML file?

    (0) 

Leave a Reply