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!
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!
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!
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!
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]);
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]);
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?
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?
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.
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.
