Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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"/>
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,
Thnx for the response, can you pls upload the QVF?
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""
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,
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.