Only the first XML element is returned

[#1]

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_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.

[flipside · Accepted solution]

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