Extract from XML. Help

[#1]

<category id="15" desc="Rent_a_car_facility">
<name language="danish">Biludlejningssted</name>
<name language="german">Autoverleih</name>
<name language="english">Car rental</name>
</category>

Hi,

I am trying to extract out the info from the xml file.

Extract out info on language and its value. eg "english" and the value is "car rental". I used the xml file wizard but I cant link "english" with "car rental"

How do I do it?

[#2]

Hi,

I did some quick reverse-engineering on the XmlSimple syntax (working on an issue with XML imports myself as well).

What works for me:

Directory;
category:
LOAD id,
 desc,
 %Key_category_08D4710147D4EF99 // Key for this table: category
FROM [extractje XML.xml] (XmlSimple, Table is [category]);
// End of [extractje XML.xml] LOAD statements

name:
LOAD
 %Key_category_08D4710147D4EF99, // Key of 'category' table
 language,
 name%Table as value
FROM [extractje XML.xml] (XmlSimple, Table is [category/name]);

The 'category' table script is automatically generated by the script wizard; the 'name' table was built by hand based on the other code that was generated by the script wizard (which I deleted in the example code!).

Things to note in the 'name' table script:

• you're reading 1 row for each category/name node in the source XML -> put 'Table is [category/name]' in the 'FROM' part
• you're reading the translated text from the node's contents (and not from a node attribute) -> this is accessible as {nodename}%Table (e.g. 'name%Table') in the script
• you're reading the language from a node attribute -> this is accessible as a 'column' {attribute-name} (e.g. 'language') in the script

The resulting code will generate a 'category' table with one row containing the values of the 'id' and 'desc' attributes of the root node, and a 'name' table containing the values of the 'language' attribute and the contents of the 'name' nodes.

Hope this helps,

Martijn

PS: I can't find any documentation on the syntax of load scripts for XML (neither XmlSimple nor XmlSax); does anyone know where to find documentation on these?

[#3]

A short reply-to-self

QlikView support confirmed that there is no documentation for the script syntax used when reading XML...

We specifically discussed the autogenerated keys; there is no way at the moment to generate these by hand, and R&D might add something for this in QV 10.

Some useful tips:

• When reading multiple XMLs, the counter used for autogenerated keys restarts with each new file -> build your own key based on the autogenerated key and the filename (e.g. 'autonumber(%key_fieldname_1234567890ABCDEF, $(vFilename))' )
• Create some template XML in which all items in the XML Schema are present (and multiple times for items that can appear multiple times), and let QlikView generate the script for this file; then edit the generated script

Regards,

Martijn

[umfoeqlik]

Thanks a lot for the Tips!

We tried a lot to cover all cases  in our XML , but we found a lot of mistakes in our qlik data. End the end it helped to build a simple XML for the creation of the LOAD script with all the cases. I guess, that Qlickview will import a couple of cases, till the pattern fits a number of times. If in a big XML other cases will occure in the later part of the file, it will not be covered by the LOAD creation script. 

So we created, out of the production data, a shorter XML in which the repititations of the knots and attributes are shown. We see, that the LOAD-Scripts were be different with every new case we added to the xml.

So yes, the combination of the two tips will be the solution to a lot of questions out there in the forum.

Thanks again