cancel
Showing results for 
Search instead for 
Did you mean: 

XMLTABLE -- WORK WITH NESTED XML DATA

venumashety
Explorer
0 Kudos
922

I am trying to convert the Nested XML File data and load into a table ( Headers and Line item Data) , As HANA does not have the supporing data type XMLPATH could not hold the nested tag data to use as table later .Here is the sample data

'<resultset> <row> <ID>1</ID> <COUNTRY>India</COUNTRY> <DATEJOINED>2017-08-11</DATEJOINED> <LineItems> <LineItem> <Number>1</Number> </LineItem> <LineItem> <Number>2</Number> </LineItem> <LineItem> <Number>3</Number> </LineItem> <LineItem> <Number>4</Number> <Number1>5</Number1> </LineItem> </LineItems> </row> <row> <ID>2</ID> <COUNTRY>India</COUNTRY> <DATEJOINED>2017-08-11</DATEJOINED> <LineItems> <LineItem> <Number>6</Number> </LineItem> <LineItem> <Number>7</Number> </LineItem> <LineItem> <Number>8</Number> </LineItem> <LineItem> <Number>9</Number> <Number1>10</Number1> </LineItem> </LineItems> </row> </resultset>'

Any thoughts / solutions how to work with nested XML Data

Kai_Mueller
Advisor
Advisor
0 Kudos

Hello Venu,

can you please explain what you try to achieve. What is the result you want to see?

In general, XMLTable supports nested data structure but can only return ONE table as always in SQL. If you need e.g. a new "table for each sub-entry", I guess you need to go over SQLScript or find another way.

Best regards, Kai

venumashety
Explorer
0 Kudos

kai.mueller01 Thanks for your response. I am trying to load the data from XML i.e from Nested XML into a table . In the Above Thread question , resultset/row contains the header data , resultset/row/LineItems/LineItem contains the line item data respective to each header , I am trying to load these data / format of these king of data .I was able to extract the header data as required but coulf not establish a relation of these header rows to line items , When i was going through couple of blogs/abot XMLTABLE ,there is a XPATH Data data in oracle where we can hold the Nested ( Lineitem tag) data /some king and use this a table and pass as input to xmltable and establish a relation but i does not work when i am trying to achieve same in HANA SQL. Here is the sample SQL : with the below SQL i get the data from header and line ietms but these are not correctly establising a relation between header and line item record WITH departments_data AS ( SELECT * FROM XMLTABLE('resultset/row' PASSING CONTENT1.DATA COLUMNS ID INT PATH 'ID', COUNTRY VARCHAR(200) PATH 'COUNTRY' ) ) ,employees_data AS ( SELECT ID, COUNTRY, * FROM departments_data, XMLTABLE('resultset/row/LineItems/LineItem' PASSING CONTENT1.DATA COLUMNS Number VARCHAR(200) PATH 'Number' ) XMLTABLE ) SELECT * FROM employees_data As HANA does not have XPATH Data type ,I am trying to see does the XMLTABLE retunrs the Line item data ( Nested ) as a column value but is returning a null result. SELECT * FROM XMLTABLE('resultset/row' PASSING CONTENT1.DATA COLUMNS ID INT PATH 'ID', COUNTRY VARCHAR(200) PATH 'COUNTRY', Number VARCHAR(200) PATH 'resultset/row/LineItems/LineItem' ) If the line iitems ( Nested) length is greater than 5000 , It does not support the Blob / clob Data type

Kai_Mueller
Advisor
Advisor
0 Kudos

Hi,

so I'm not sure if you can achieve your scenario with HANA. My understanding is, that you want a table like: ID, COUNTRY, LINEITEM_NUMBER, right? Here are my thoughts:

WITH xml_data AS (
	SELECT '<resultset>
	<row>
		<ID>1</ID>
		<COUNTRY>India</COUNTRY>
		<DATEJOINED>2017-08-11</DATEJOINED>
		<LineItems>
			<LineItem>
				<Number>1</Number>
			</LineItem>
			<LineItem>
				<Number>2</Number>
			</LineItem>
			<LineItem>
				<Number>3</Number>
			</LineItem>
			<LineItem>
				<Number>4</Number>
				<Number1>5</Number1>
			</LineItem>
		</LineItems>
	</row>
	<row>
		<ID>2</ID>
		<COUNTRY>India</COUNTRY>
		<DATEJOINED>2017-08-11</DATEJOINED>
		<LineItems>
			<LineItem>
				<Number>6</Number>
			</LineItem>
			<LineItem>
				<Number>7</Number>
			</LineItem>
			<LineItem>
				<Number>8</Number>
			</LineItem>
			<LineItem>
				<Number>9</Number>
				<Number1>10</Number1>
			</LineItem>
		</LineItems>
	</row>
</resultset>' AS XML_DATA FROM DUMMY
)
SELECT
	* 
FROM 
	XMLTABLE(
		'resultset/row' PASSING XML_DATA.XML_DATA 
		COLUMNS 
			ID INT PATH 'ID',
			COUUNTRY VARCHAR(200) PATH 'COUNTRY',
			LINE_ITEMS NVARCHAR(5000) FORMAT XML PATH 'LineItems'
	)

The result will contain the id, the country and the line items as XML. You are right, this will fail with strings longer than 5000 characters since NCLOB is not supported.

So my next thought was to pass this result into a next round of XMLTABLE

WITH xml_data AS (
	SELECT 1 AS ID, '
		<LineItems>
			<LineItem>
				<Number>6</Number>
			</LineItem>
			<LineItem>
				<Number>7</Number>
			</LineItem>
			<LineItem>
				<Number>8</Number>
			</LineItem>
			<LineItem>
				<Number>9</Number>
				<Number1>10</Number1>
			</LineItem>
		</LineItems>
' AS XML_DATA FROM DUMMY
)
SELECT
	* 
FROM 
	XMLTABLE(
		'LineItems/LineItem' PASSING XML_DATA.XML_DATA 
		COLUMNS 
			ID INT ???,
			Number INT PATH 'Number'
	)

But this will NOT work, because any data except the XML in the table cannot be access, meaning that you lose the link to the ID and the country. In addition, another idea would be to use changing XPaths for each row, but this is as well not supported.

This means, you can either get all the header data or all line items or all line items from a specific header but not all in a linked way.

A possibility would be to build this with SQLScript where you have stuff like loops and dynamic SQL. Another thought but which needs changes to the XML would be, to a kind of ID to each line item, so that you match them to the header later.

Best regrads, Kai

venumashety
Explorer
0 Kudos

Thanks , @kai.mueller01 , Do we know when HANA would release returning the data type which includes Blob/clob.

Check this blog out of oracle where by using the XML TABLE function there were able to establish a relation between header and line items where there is no link.

https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql

Not sure what is being missed out when we try to re-implement the same in HANA , it does not work as expected

Kai_Mueller
Advisor
Advisor
0 Kudos

Hi,

so currently I would say it's works as designed but with documented limitations. I'm not aware of any plan to add support for LOB datatypes or cross dependencies.

Best regards, Kai

venumashety
Explorer
0 Kudos

Thanks , @kai.mueller01

Accepted Solutions (0)

Answers (1)

Answers (1)

chr_br
Explorer
0 Kudos

Hi venumashety, Hi kai.mueller01,

I was faced with a similar problem.

I solved the problem by using "RN" FOR ORDINALITY to number the rows and saving the result in a temporary table.

Then I created a second temporary table, which gave me via LEAD function the first row of the ID and the row before the next ID.

Example:

select id, rn, LEAD(rn) OVER (ORDER BY rn asc) as rn_lead

The result can then be joined to the first temporary table by taking all the row numbers that lie between RN and LEAD(rn).

I hope it was understandable and helps.

Best regards,

Christian