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

jmcy2408 — Fri, 16 Feb 2024 08:36:08 GMT

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);

topic XML Response from MySQL - FROM_FIELD (Source_Table,Source_Field)(xmlGeneric) in App Development

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