Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load XML with nested children into a flat table

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:

idarticle_keyhouseregnrtypearnrnamepricesumrtypegrpuarrudep
126731_020_00052326731000523020657Article Name 122.9045.8400012648132001264899600
226731_020_00052326731000523020658Article Name 222.9045.8400012648132001264899600
3920123_020_8365392012383653020241Article Name 322.9045.8100012648132001264899600
.......................................


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


Thanks for any help in advance!


Best Regards

1 Reply
stiffi88
Partner - Contributor III
Partner - Contributor III

Hi Kevin,

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

You can use Dateformat.

2017-09-14 17_27_36-Qlik Sense Desktop.png

This will be generated on the load-script:

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

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

KR

Clemens