topic Re: Fields Parsing from XML in App Development

Fields Parsing from XML

Rehan — Wed, 21 Oct 2020 02:22:42 GMT

I am using Qlik Sense April 2020 Patch 8 Enterprise. I have pulled in the data from API in the form of a XML string as below(Sample)

<ns:getHistoryResponse xmlns:ns="http://Mywebsite.com/xsd"><ns:return><?xml version="1.0" encoding="UTF-8"?><RESULTS><STATUS MESSAGE="Normal Completion">Success</STATUS><AUTH_HISTORIES><AUTH_HISTORY DATES="2015-04-06 13:14:19" FIELDS="ATO Upcoming" NEW_VALUE="07/31/2015" OLD_VALUE="01/23/2018" SYSTEM_ID="9388"/><AUTH_HISTORY DATES="2015-08-14 13:21:36" FIELDS="ATO Status" NEW_VALUE="ATO (1yr)" OLD_VALUE="Expired" SYSTEM_ID="9388"/><AUTH_HISTORY DATES="2015-08-14 13:21:37" FIELDS="ATO Upcoming" NEW_VALUE="08/14/2016" OLD_VALUE="07/31/2015".

My Auto Generated script is as below

LOAD
XMLString
FROM [lib://My Connection]
(XmlSimple, table is getHistoryResponse);

I would like to parse out the below fields from the above XML. How I can accomplish that

LOAD SYSTEM_ID,
OLD_VALUE,
NEW_VALUE,
FIELDS,
DATES;

Re: Fields Parsing from XML

ArnadoSandoval — Wed, 21 Oct 2020 03:53:22 GMT

Hi @Rehan

There are some issues with the sample data, I highlighted them:

Issues:

<ns:getHistoryResponse xmlns:ns="http://Mywebsite.com/xsd"><ns:return><?xml version="1.0" encoding="UTF-8"?><RESULTS><STATUS MESSAGE="Normal Completion">Success</STATUS><AUTH_HISTORIES><AUTH_HISTORY DATES="2015-04-06 13:14:19" FIELDS="ATO Upcoming" NEW_VALUE="07/31/2015" OLD_VALUE="01/23/2018" SYSTEM_ID="9388"/><AUTH_HISTORY DATES="2015-08-14 13:21:36" FIELDS="ATO Status" NEW_VALUE="ATO (1yr)" OLD_VALUE="Expired" SYSTEM_ID="9388"/><AUTH_HISTORY DATES="2015-08-14 13:21:37" FIELDS="ATO Upcoming" NEW_VALUE="08/14/2016" OLD_VALUE="07/31/2015"/>

Red Text: You do not need the ns:getHistoryResponse and ns:return> as part of the XML data for Qlik to load it.
Orange Text: You should convert < to < (yes, the lower than sign <)
Green Text: This tags are missing their closing tag; perhaps they disappeared when you pasted the code.
Blue Text: the tag was not close.

Once these issues are fixed, your file should look as shown below:

<?xml version="1.0" encoding="UTF-8"?> <RESULTS> <STATUS MESSAGE="Normal Completion">Success</STATUS> <AUTH_HISTORIES> <AUTH_HISTORY DATES="2015-04-06 13:14:19" FIELDS="ATO Upcoming" NEW_VALUE="07/31/2015" OLD_VALUE="01/23/2018" SYSTEM_ID="9388"/> <AUTH_HISTORY DATES="2015-08-14 13:21:36" FIELDS="ATO Status" NEW_VALUE="ATO (1yr)" OLD_VALUE="Expired" SYSTEM_ID="9388"/> <AUTH_HISTORY DATES="2015-08-14 13:21:37" FIELDS="ATO Upcoming" NEW_VALUE="08/14/2016" OLD_VALUE="07/31/2015"/> </AUTH_HISTORIES> </RESULTS>

The file above load fine, this is the load script for it:

Auth_History: LOAD DATES, "FIELDS", NEW_VALUE, OLD_VALUE, SYSTEM_ID, %Key_RESULTS_30D6815018607F0E FROM [lib://DataFiles/XML_Data_Good.xml] (XmlSimple, table is [RESULTS/AUTH_HISTORIES/AUTH_HISTORY]); NoConcatenate Results: LOAD STATUS, "STATUS/MESSAGE", %Key_RESULTS_30D6815018607F0E FROM [lib://DataFiles/XML_Data_Good.xml] (XmlSimple, table is RESULTS);

This is the data model for your file:

Hope this helps,

Re: Fields Parsing from XML

Rehan — Wed, 21 Oct 2020 04:25:32 GMT

Thnx for the response, can you pls upload the QVF?

Re: Fields Parsing from XML

Rehan — Thu, 22 Oct 2020 12:47:34 GMT

The actual Code is

"<?xml version=""1.0"" encoding=""UTF-8""?><RESULTS><STATUS MESSAGE=""Normal Completion"">Success</STATUS><AUTH_HISTORIES><AUTH_HISTORY DATES=""2015-04-06 13:14:19"" FIELDS=""ATO Upcoming"" NEW_VALUE=""07/31/2015"" OLD_VALUE=""01/23/2018"" SYSTEM_ID=""9388""/><AUTH_HISTORY DATES=""2015-08-14 13:21:36"" FIELDS=""ATO Status"" NEW_VALUE=""ATO (1yr)"" OLD_VALUE=""Expired"" SYSTEM_ID=""9388""/><AUTH_HISTORY DATES=""2015-08-14 13:21:37"" FIELDS=""ATO Upcoming"" NEW_VALUE=""08/14/2016"" OLD_VALUE=""07/31/2015""

Re: Fields Parsing from XML

ArnadoSandoval — Fri, 23 Oct 2020 11:39:44 GMT

Hi @Rehan

I did not receive any notification from your reply, I guess you should include my tag @ArnaldoSandoval when writing your reply.

I am attaching the XML file (as a zip file) as well as my Test application, NOTE: just look at the script, as I did not create any sheet with your data.

Now regarding the sample data in your last reply, for unknown reason all the values are enclosed in double-double quote, like ""ATO Upcoming"" instead of "ATO Upcoming", your last AUTH_HISTORY is not complete, it is missing /> and your are not closing the AUTH_HISTORIES and RESULT tags.

Hope this helps,

Re: Fields Parsing from XML

Rehan — Fri, 23 Oct 2020 12:24:07 GMT

I am getting the double quotes when I am storing it as a xml file. If I save it as a .txt I am good but saving as txt is adding the single Quote at the start and end of the document. Is there a way I can get rid of that single quote.

Please see the attached screen shot.