2 Replies Latest reply: Jan 21, 2010 8:04 AM by Martijn ter Schegget RSS

    Extract from XML. Help

    faithteng

      <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?

       

       

        • Extract from XML. Help
          Martijn ter Schegget

          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?

            • Extract from XML. Help
              Martijn ter Schegget

              A short reply-to-self Big Smile

              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