Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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