Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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