I have a file with several fields and 1 of the fields is in XML format. How do I import the field in XML format so that it is readable to the end-user?
I am attaching an Excel file; the field named TriggerDetailXml is the field that I want to Display with the following fields:
Any help would greatly be appreciated. Thanks in advance!
Hi, If your source is DB, then use the XMLTYPE sql functions to get the data from xml fields.
If your source is Files, then you can need write custom script to pull the data from xml type field. Please try below methodology.
1. Load the file into Qlikview and add the Rowno() function to create unique key.
2. Generate Loop for each row and load the data from xml field make separate table.
3. Join the new tables into Source table on Unique key field.
If you got the solution, please close the thread by marking the useful comment as Correct or Helpful. It will be useful for others in future.
You can also use the standard (xmlsimple) loading from_field as shown in this thread:
In your case, you need to a bit more work, The xml field is treated as one xml document, and all the <Trigger> elements need to be contained by an outside element like <Triggers> (plural). You can create the contained version like:
'<Triggers>' & concat(TriggerDetailXml) & '</Triggers>' as TriggersTemp
Then you can reference this field in a subsequent LOAD like:
from_field (XmlTemp,TriggersTemp) (XmlSimple, Table is [Triggers/Trigger]);
The example you sent works and makes sense, but I have other XML code formatted a bit differently that isn't working.
I'm attaching another example: I've added tab Sheet1 to the attached Excel file and added tab Example2 to the QVW file. It seems the same function doesn't work for this XMC code. Can you please take a look at let me know the best way to get the data from this XML? Should I use the subfield function instead?
Thanks in advance for your assistance.
I could show you how to load it, but the data has a fatal flaw. The TriggerDetailXml field in the third row of the xls is truncated ar 32743 characters. It is missing data and therefore not valid XML. I believe excel limits a cell data to max length of 32,767 which is probably where the problem is created.
So you are out of luck using the xml parser on this datasource. You could revert to using the substring() or TextBetween() method to maybe get what you want, if all the necessary data is there.
The attached file shows that the xmlparser will work for the first two rows, but not the third. I had to remove the xml document declaration string "<?xml version="1.0" encoding="utf-8"?>" using the replace function as this can only appear once.