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

Only the first XML element is returned

Hello,

I want to load an XML which looks like this :

<providers>

    <provider>

        <name>Provider1</name>

        <categories>

            <category>Books</category>

            <categoy>Clothes</category>

        </categories>

    </provider>

    <provider>

        <name>Provider2</name>

        <categories>

            <category>Grocery</category>

            <categoy>Bags</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_namecat
Provider1Books
Provider2Grocery

But the results I want should look like this :

provider_namecat
Provider1Books
Provider1Clothes
Provider2Grocery
Provider2Bags

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

Thank you.

1 Solution

Accepted Solutions
flipside
Partner - Specialist II
Partner - Specialist II

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

View solution in original post

1 Reply
flipside
Partner - Specialist II
Partner - Specialist II

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