XML Response from MySQL - FROM_FIELD (Source_Table,Source_Field)(xmlGeneric)

[jmcy2408]

Hi, 

Working with a new source, hosted by a third party.

The data is stored within a XML field (varying structures - defined by the type of task a user is completing and associated workflow).

Within MySQL i've been using the ExtractValue function to get the user entry's for each XPath but it's a little cumbersome. I've been looking at FROM_FIELD within Qlik which is looking promising,  the response cannot work with XML or XMLSimple (it needs to be dynamics so not able to LOAD *, this wont work, but XMLGeneric - gives me the Object, Attribute and Value fields which works for what i need.

However i cannot link this table back to the source, is there way in the LOAD * FROM_FIELD i can add a reference point to the row in the source the XLM relates to?

I've tried a few things, loops, referencing a common node in all entries but nothing works.

Help greatly appreciated.

Example code from MySQL

SOURCE:

SELECT 

ID,

VERSION,

TASK_ID,

TASK_COMPLETION_DETAILS as XML

FROM 

TASK_COMPLETION_RESULT;

Sample XML:

<?xml version="1.0" encoding="UTF-8"?>
<submission xmlns="http://opendatakit.org/submissions" xmlns:orx="http://openrosa.org/xforms">
   <data>
      <data id="abort_flow" instanceID="uuid:b6c91e34-c731-44af-b789-a13c1ebfffc3" version="20210507" submissionDate="2024-02-11T17:58:50.711Z" isComplete="true" markedAsCompleteDate="2024-02-11T17:58:50.711Z">
         <utility_type>solar</utility_type>
         <abort_group>
            <abort_reason_1>job_cancelled_by_client</abort_reason_1>
            <abort_reason_2/>
            <abort_reason_3/>
            <abort_reason_4/>
            <abort_reason_5/>
            <abort_detalis>cancelled short notice by client due to availability of raw materials.</abort_detalis>
         </abort_group>
         <photo_repeat>
            <additional_info_r>
               <additional_photo />
               <additional_photo_notes />
            </additional_info_r>
         </photo_repeat>
         <orx:meta>
            <orx:instanceID>uuid:b6c91e34-c731-44af-b789-a13c1ebfffc3</orx:instanceID>
         </orx:meta>
      </data>
   </data>
</submission>

The <data id=..> element is the same in all records, what then follows is then dynamic related to the task.

InstanceID in the XML refers back to the TASK_ID in the table.

XML_ATTRIBUTE_VALUE:

LOAD *

FROM_FIELD 

(SOURCE,XML)(xmlGeneric);