XML - Reading data - Parent Elements - Qlik Community

DavidFoster1 · ‎2014-04-24

One for the QV XML gurus

In an XML Load statement is it possible to make references to attributes of elements above the table-element in your FROM statement. I want to define my own foreign keys by looking a the ID attribute of the parent element.

Thoughts?

Colin-Albert · ‎2014-04-24

Hi David,

I believe that the QlikView XML option will only access TABLE tags.

You could extract the data as text and process it through TEXTBETWEEN functions to extract other tags outside the TABLE tags, possibly with other functions like SUBFIELD to extract the data.

DavidFoster1 · ‎2014-09-09

This is has raised its ugly head again. Any assistance gratefully received.

flipside · ‎2014-09-09

I'm not 100% sure on what you are trying to do, but could you amend the xml using the load script like this ...

xmlraw:

Load '<UserPrefs>' as '<head>' autogenerate 1;
Load '<UserId>abcdef</UserId>' as '<head>' autogenerate 1;
Load '</UserPrefs>' as '<head>' autogenerate 1;
LOAD @1:n as '<head>'
FROM
[AllProperties.xml] (fix, utf8);
Load '</head>' AS '<head>' autogenerate 1;

STORE xmlraw into [AllProperties2.xml] (txt);

flipside

DavidFoster1 · ‎2014-09-11

The approach I have used in the end was to loop through the XML files one by one and use LEFT JOINs using the derived XML keys and the real foreign key to add the keys to the child tables. I did try ApplyMap but I couldn't drop the mapping tables for each loop which caused incorrect mappings.