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

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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