Only the first XML element is returned

Tue, 22 Jul 2014 08:49:50 GMT

Hello,

I want to load an XML which looks like this :

<name>Provider1</name>

<category>Books</category>

<categoy>Clothes</category>

</categories>

</provider>

<name>Provider2</name>

<category>Grocery</category>

</categories>

</provider>

</providers>

If I load this file with the following script, it only returns the first element of <categories>.

LOAD

[categories/category] AS cat,

name AS provider_name

FROM (XmlSimple, Table is [providers/provider]);

The results are :

provider_name	cat
Provider1	Books
Provider2	Grocery

But the results I want should look like this :

provider_name	cat
Provider1	Books
Provider1	Clothes
Provider2	Grocery
Provider2	Bags

How can I loop through all of the <category> elements ?

Thank you.

Re: Only the first XML element is returned

flipside — Tue, 22 Jul 2014 10:11:26 GMT

Hi Anita,

Firstly, the second (opening) category tag is spelt incorrectly, but when corrected, this script works ...

LOAD

name AS provider_name,
%Key_provider_FE443E36DFD0B754
FROM [tmp.xml] (XmlSimple, Table is [providers/provider]);

left join

LOAD category%Table as cat,
%Key_provider_FE443E36DFD0B754
FROM [tmp.xml] (XmlSimple, Table is [providers/provider/categories/category]);

DROP FIELD %Key_provider_FE443E36DFD0B754;

Not sure how the key name is generated so you may need to load the xml in using the wizard to find it (Data from files > Table Files... > {select xml file} > File Type = Xml)

flipside

topic Only the first XML element is returned in QlikView

Only the first XML element is returned

Re: Only the first XML element is returned