Processing XML data in SAP HANA

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

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.

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.