Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Need assistance for importing data with XML

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:

  • TriggerTypeid
  • Scheduler
  • Frequency
  • Rules
  • TriggerAttributes
  • etc.

Any help would greatly be appreciated.  Thanks in advance!

9 Replies
Not applicable

Re: Need assistance for importing data with XML

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.

Not applicable

Re: Need assistance for importing data with XML

Hi Dathu,

Would you be able to provide an example for the custom script (to pull the data from xml type field)? 

Re: Need assistance for importing data with XML

Hi Robyn,

Check the attachment and let me know. !

Not applicable

Re: Need assistance for importing data with XML

This is terrific!  Thank you for the example.

Re: Need assistance for importing data with XML

Hi Robyn,

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.

Thank you.

Re: Need assistance for importing data with XML

You can also use the standard (xmlsimple) loading from_field as shown in this thread:

Re: Reading multiple XML nodes

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:

XmlTemp:

LOAD

  '<Triggers>' & concat(TriggerDetailXml) & '</Triggers>' as TriggersTemp

Resident Data;

Then you can reference this field in a subsequent LOAD like:

LOAD ...

from_field (XmlTemp,TriggersTemp) (XmlSimple, Table is [Triggers/Trigger]);

Example attached.

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable

Re: Need assistance for importing data with XML

Hi Rob,

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.

Re: Need assistance for importing data with XML

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.

-Rob

everest226
Contributor III

Re: Need assistance for importing data with XML

this was very helpful