Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
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
Highlighted
Partner
Partner

Re: Load XML with nested children into a flat table

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