Load XML with nested children into a flat table

Thu, 14 Sep 2017 13:11:45 GMT

Hi,

We have a MySQL table with one field containing an XML string. The structure of the XML can be seen in the attached XML file. Basically, it looks like this:

<articles>
<article>...</article>
<article>...</article>
...
</articles>

I start by loading this XML field like below:

t_stat_temp:
SQL SELECT house, type, regnr, booking FROM t_stat;

Then I add the house, type and regnr to that XML. These three are our parent key for all <articles>.

t_stat_temp2:
LOAD
'<stat><stat_row>'&
concat('<regnr>'&regnr&'</regnr><house>'&house&'</house><type>'&type&'</type>'&booking,'</stat_row><stat_row>')
&'</stat_row></stat>' as stat_concat
RESIDENT t_stat_temp
WHERE booking > '';

LET stat_concat = peek('stat_concat');

I then tried to load all these fields into one single flat table:

LOAD
house&'_'&type&'_'&regnr as article_key,
type as type,
regnr as regnr,
house as house,
[articles/article/arnr] as arnr,
[articles/article/name] as name,
[articles/article/price] as price,
[articles/article/sum] as sum,
[articles/article/rtype] as rtype,
[articles/article/grp] as grp,
[articles/article/uarr] as uarr,
[articles/article/udep] as udep
INLINE '$(stat_concat)' (XmlSimple, table is [stat/stat_row], unicode);

Unfortunately this failed. Only the very first article was loaded, all the others were ignored. Eventually I want the final table to look like this:

id	article_key	house	regnr	type	arnr	name	price	sum	rtype	grp	uarr	udep
1	26731_020_000523	26731	000523	020	657	Article Name 1	22.90	45.8	40	00	1264813200	1264899600
2	26731_020_000523	26731	000523	020	658	Article Name 2	22.90	45.8	40	00	1264813200	1264899600
3	920123_020_83653	920123	83653	020	241	Article Name 3	22.90	45.8	10	00	1264813200	1264899600
...	...	...	...	...	...	...	...	...	...	...	...	...

The id should be generated automatically somehow. How can I achieve this?

Thanks for any help in advance!

Best Regards

Re: Load XML with nested children into a flat table

stiffi88 — Thu, 14 Sep 2017 15:28:32 GMT

Hi Kevin,

If you have an XML-File you can laod it normaly.

You can use Dateformat.

This will be generated on the load-script:

FROM [lib://test/preview.xml]

(XmlSimple, table is [stat/stat_row/articles/article]);

Clemens

topic Re: Load XML with nested children into a flat table in QlikView

Load XML with nested children into a flat table

Re: Load XML with nested children into a flat table