topic XML - Reading data - Parent Elements in QlikView

XML - Reading data - Parent Elements

DavidFoster1 — Thu, 24 Apr 2014 16:20:25 GMT

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?

Re: XML - Reading data - Parent Elements

Colin-Albert — Thu, 24 Apr 2014 16:52:38 GMT

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.

Re: XML - Reading data - Parent Elements

DavidFoster1 — Tue, 09 Sep 2014 09:06:29 GMT

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

Re: XML - Reading data - Parent Elements

flipside — Tue, 09 Sep 2014 09:44:11 GMT

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

Re: XML - Reading data - Parent Elements

DavidFoster1 — Thu, 11 Sep 2014 09:43:10 GMT

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.