Fields Parsing from XML

[Rehan]

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>&lt;?xml version="1.0" encoding="UTF-8"?>&lt;RESULTS>&lt;STATUS MESSAGE="Normal Completion">Success&lt;/STATUS>&lt;AUTH_HISTORIES>&lt;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"/>&lt;AUTH_HISTORY DATES="2015-08-14 13:21:36" FIELDS="ATO Status" NEW_VALUE="ATO (1yr)" OLD_VALUE="Expired" SYSTEM_ID="9388"/>&lt;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;

[ArnadoSandoval]

Hi @Rehan 

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

Issues:

<ns:getHistoryResponse xmlns:ns="http://Mywebsite.com/xsd"><ns:return>&lt;?xml version="1.0" encoding="UTF-8"?>&lt;RESULTS>&lt;STATUS MESSAGE="Normal Completion">Success&lt;/STATUS>&lt;AUTH_HISTORIES>&lt;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"/>&lt;AUTH_HISTORY DATES="2015-08-14 13:21:36" FIELDS="ATO Status" NEW_VALUE="ATO (1yr)" OLD_VALUE="Expired" SYSTEM_ID="9388"/>&lt;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 &lt; 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,

[Rehan]

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

[Rehan]

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""

[ArnadoSandoval]

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,

[Rehan]

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.