Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
Author

Hi Dathu,

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

tamilarasu
Champion
Champion

Hi Robyn,

Check the attachment and let me know. !

Not applicable
Author

This is terrific!  Thank you for the example.

tamilarasu
Champion
Champion

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Author

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator III
Creator III

this was very helpful