Hello @bbbutler,
Here is an XML tutorial that may help you get started with reading XML and transforming it:
https://knowledge.safe.com/articles/29553/tutorial-getting-started-with-xml.html
Hi @bbbutler ,
The XML tutorial Trent posted is a good place to start. Unfortunately the source XML you need to read from has a rather highly normalized, complex structure, making it a challenge to work with. Basically it has one metadata element with field names and then a series of row elements with value series that correspond to the field names in the metadata element. So what you end up with is a field name list and then a series of value lists. The problem then isn't so much reading the XML as it is transforming the lists that are read into flat FME features.
incidentxml-to-csv.zip
The attached workspace demonstrates 2 approaches. You can just read the row values into one list per feature, then use a ListExploder, AttributeCreator and Aggregator to pivot the list into col0 to col21. Then its a matter of manually mapping this to your destination schema. Or you can use a dynamic schema approach that reads the metadata and value lists, merges them into item{}.name and item{}.value - name / value pairs, and then uses the FeatureBuilder custom transformer from the Hub to generate records from these lists. Unfortunately, at the moment there is some python involved here as FME has limitations when trying to convert between unstructured and structured lists.
If you renamed each <value> element to a proper item name, you could create your desired features easily by XML fragmentation and flattening - XMLFragmenter. Based on this idea, I created this workspace example:
xmlxqueryudpdater-xmlfragmenter-bulkattributerenam.fmwt (FME 2017.0.1.1)
If you renamed each <value> element to a proper item name, you could create your desired features easily by XML fragmentation and flattening - XMLFragmenter. Based on this idea, I created this workspace example:
xmlxqueryudpdater-xmlfragmenter-bulkattributerenam.fmwt (FME 2017.0.1.1)
Thank you @takashi this worked perfectly! You are amazing!
Hi @bbbutler ,
The XML tutorial Trent posted is a good place to start. Unfortunately the source XML you need to read from has a rather highly normalized, complex structure, making it a challenge to work with. Basically it has one metadata element with field names and then a series of row elements with value series that correspond to the field names in the metadata element. So what you end up with is a field name list and then a series of value lists. The problem then isn't so much reading the XML as it is transforming the lists that are read into flat FME features.
incidentxml-to-csv.zip
The attached workspace demonstrates 2 approaches. You can just read the row values into one list per feature, then use a ListExploder, AttributeCreator and Aggregator to pivot the list into col0 to col21. Then its a matter of manually mapping this to your destination schema. Or you can use a dynamic schema approach that reads the metadata and value lists, merges them into item{}.name and item{}.value - name / value pairs, and then uses the FeatureBuilder custom transformer from the Hub to generate records from these lists. Unfortunately, at the moment there is some python involved here as FME has limitations when trying to convert between unstructured and structured lists.
Thanks @DeanAtSafe this is also awesome!
Hi @bbbutler ,
The XML tutorial Trent posted is a good place to start. Unfortunately the source XML you need to read from has a rather highly normalized, complex structure, making it a challenge to work with. Basically it has one metadata element with field names and then a series of row elements with value series that correspond to the field names in the metadata element. So what you end up with is a field name list and then a series of value lists. The problem then isn't so much reading the XML as it is transforming the lists that are read into flat FME features.
incidentxml-to-csv.zip
The attached workspace demonstrates 2 approaches. You can just read the row values into one list per feature, then use a ListExploder, AttributeCreator and Aggregator to pivot the list into col0 to col21. Then its a matter of manually mapping this to your destination schema. Or you can use a dynamic schema approach that reads the metadata and value lists, merges them into item{}.name and item{}.value - name / value pairs, and then uses the FeatureBuilder custom transformer from the Hub to generate records from these lists. Unfortunately, at the moment there is some python involved here as FME has limitations when trying to convert between unstructured and structured lists.
Hi @DeanAtSafe,
> to convert between unstructured and structured lists.
I sometimes use a BulkAttributeRenamer to do that.
In the above example, it would be ideal if "item{}" could be hidden after renaming. However, unfortunately, currently there is no transformers that have the "Attributes/Lists to Hide" option, except the PythonCaller and the TclCaller. I hope the option parameter will be added to some other transformers too. See also these Ideas.
If you renamed each <value> element to a proper item name, you could create your desired features easily by XML fragmentation and flattening - XMLFragmenter. Based on this idea, I created this workspace example:
xmlxqueryudpdater-xmlfragmenter-bulkattributerenam.fmwt (FME 2017.0.1.1)
Another approach. JSON processing can be applied to convert two lists separately storing names and values to a set of regular attributes. See this workspace example to learn more.
jsontemplater-jsonflattener.fmwt (FME 2017.0.1.1)
If you renamed each <value> element to a proper item name, you could create your desired features easily by XML fragmentation and flattening - XMLFragmenter. Based on this idea, I created this workspace example:
xmlxqueryudpdater-xmlfragmenter-bulkattributerenam.fmwt (FME 2017.0.1.1)
One more. XQuery expression can convert XML to JSON directly.
xmlxqueryupdater-jsonfragmenter.fmwt (FME 2017.0.1.1)
