Hi @bhornung, concrete solution depends on the schema of the XML document, but I think the XMLFragmenter or the XMLFlattener might help you in many cases.
For example, if the source XML looks like this,
<?xml version="1.0"?>
<records>
<record>
<tag1>value01</tag1>
<tag2>value02</tag2>
</record>
<record>
<tag1>value11</tag1>
<tag2>value12</tag2>
</record>
</records>
the XMLFragmenter (Elements to Match: record, Flatten Options: Enable Flattening) outputs two features containing these attributes.
The 1st Feature has:
Attribute(encoded: utf-16): `tag1' has value `value01'
Attribute(encoded: utf-16): `tag2' has value `value02'
The 2nd Feature has:
Attribute(encoded: utf-16): `tag1' has value `value11'
Attribute(encoded: utf-16): `tag2' has value `value12'
If you would need more specific answers, please post a small XML example and specifically explain about what result you need to get from the XML
Hi @bhornung, concrete solution depends on the schema of the XML document, but I think the XMLFragmenter or the XMLFlattener might help you in many cases.
For example, if the source XML looks like this,
<?xml version="1.0"?>
<records>
<record>
<tag1>value01</tag1>
<tag2>value02</tag2>
</record>
<record>
<tag1>value11</tag1>
<tag2>value12</tag2>
</record>
</records>
the XMLFragmenter (Elements to Match: record, Flatten Options: Enable Flattening) outputs two features containing these attributes.
The 1st Feature has:
Attribute(encoded: utf-16): `tag1' has value `value01'
Attribute(encoded: utf-16): `tag2' has value `value02'
The 2nd Feature has:
Attribute(encoded: utf-16): `tag1' has value `value11'
Attribute(encoded: utf-16): `tag2' has value `value12'
If you would need more specific answers, please post a small XML example and specifically explain about what result you need to get from the XML
Hi @takashi, Thanks for taking interest!
We already have successfully flattened the XML into FME variables\fields. The original XML looks like this:
<?xml version="1.0"?>
<Inspect>
<Project name="Collect Project -REG00069122_180222114519" type="AssetUpdate">
<AssignedTo>username</AssignedTo>
<Location>
<Geometry type="undefined"/>
<OriginalGeometry type="undefined"/>
<MapObject/>
</Location>
<Attributes/>
<ProjectResults>
<Results/>
<ChildResults/>
</ProjectResults>
<Drawings/>
</Project>
<InspectObjects>
<Object id="1111111" action="update" objectType="transformer_bank_urban_location" DataSetName="electric" UniverseId="0" WorldId="0">
<Attributes>
<Attribute id="id">11111111</Attribute>
<Attribute id="status">Existing</Attribute>
<Attribute id="source_network_type">MV</Attribute>
<Attribute id="source_nominal_voltage_pp">14.400 kV</Attribute>
</Attributes>
<AssociatedAttributes/>
<MapObject>
<ClassInfo>M247</ClassInfo>
</MapObject>
<Location>
<Geometry type="point">
<Point units="cm">
<X>44906183</X>
<Y>589417122</Y>
</Point>
</Geometry>
<OriginalGeometry type="point">
<Point units="cm">
<X>44906183</X>
<Y>589417122</Y>
</Point>
</OriginalGeometry>
<MapObject/>
</Location>
<Attachments>
<Attachment id="1_2874934531.jpg"/>
<Attachment id="32_2874934531.jpg"/>
<Attachment id="31_2874934531.jpg"/>
<Attachment id="2_2874934531.jpg"/>
<Attachment id="30_2874934531.jpg"/>
<Attachment id="29_2874934531.jpg"/>
<Attachment id="28_2874934531.jpg"/>
<Attachment id="27_2874934531.jpg"/>
<Attachment id="26_2874934531.jpg"/>
</Attachments>
FME then parses them to a list variable\array:
Attachments.Attachment{0}.id = 1_2874934531.jpg
Attachments.Attachment{1}.id = 32_2874934531.jpg
Attachments.Attachment{2}.id = 31_2874934531.jpg
etc....
What I am looking for is a method of inserting each photo name into a new record in a SQL database. Currently for each feature that gets processed through FME, we insert it's various attributes into corresponding SQL fields. This is all done with one single SQL insert statement which results in one SQL table record per feature. With the photo's however, there is an indefinite amount of photos that each need to be inserted into their own record (one SQL record per photo). My problem is that each feature may have 0-->infinite amounts of photo's associated with it.
Essentially the logic of the XML conversion is that an XML contain one or more asset inspection records. For each record, the xml is flattened into variables\fields then inserted into a record in the corresponding SQL tables using the 'SQLExecutor'. This all works great until we now need to insert one record per photo (indefinite), but all related to only one XML record's process through FME (all done through a single SQLExecutor).
Hope that makes sense:)
Hi @bhornung, concrete solution depends on the schema of the XML document, but I think the XMLFragmenter or the XMLFlattener might help you in many cases.
For example, if the source XML looks like this,
<?xml version="1.0"?>
<records>
<record>
<tag1>value01</tag1>
<tag2>value02</tag2>
</record>
<record>
<tag1>value11</tag1>
<tag2>value12</tag2>
</record>
</records>
the XMLFragmenter (Elements to Match: record, Flatten Options: Enable Flattening) outputs two features containing these attributes.
The 1st Feature has:
Attribute(encoded: utf-16): `tag1' has value `value01'
Attribute(encoded: utf-16): `tag2' has value `value02'
The 2nd Feature has:
Attribute(encoded: utf-16): `tag1' has value `value11'
Attribute(encoded: utf-16): `tag2' has value `value12'
If you would need more specific answers, please post a small XML example and specifically explain about what result you need to get from the XML
Since you have succeeded to store the jpg file names into a list attribute "Attachments.Attachment{}.id" already, you can extract each value as non-list attribute with just using the ListExploder. Note: Possibly you have to expose the list name "Attachments.Attachment{}.id" before applying the ListExploder, with the AttributeExposer (or the Attributes to Expose parameter in the XML transformer)
Hi @bhornung, concrete solution depends on the schema of the XML document, but I think the XMLFragmenter or the XMLFlattener might help you in many cases.
For example, if the source XML looks like this,
<?xml version="1.0"?>
<records>
<record>
<tag1>value01</tag1>
<tag2>value02</tag2>
</record>
<record>
<tag1>value11</tag1>
<tag2>value12</tag2>
</record>
</records>
the XMLFragmenter (Elements to Match: record, Flatten Options: Enable Flattening) outputs two features containing these attributes.
The 1st Feature has:
Attribute(encoded: utf-16): `tag1' has value `value01'
Attribute(encoded: utf-16): `tag2' has value `value02'
The 2nd Feature has:
Attribute(encoded: utf-16): `tag1' has value `value11'
Attribute(encoded: utf-16): `tag2' has value `value12'
If you would need more specific answers, please post a small XML example and specifically explain about what result you need to get from the XML
Alternatively, an XMLXQueryExtractor can be used to generate semicolon-separated multiple SQL statements directly from the source XML document. For example, assuming the XML looks like below,
<?xml version="1.0"?>
<Inspect>
<InspectObjects>
<Object id="AAA">
<Attachments>
<Attachment id="a.jpg"/>
<Attachment id="b.jpg"/>
</Attachments>
</Object>
<Object id="BBB">
<Attachments>
<Attachment id="c.jpg"/>
<Attachment id="d.jpg"/>
</Attachments>
</Object>
</InspectObjects>
</Inspect>
an XMLXQueryExtractor with this XQuery expression creates a new attribute storing the following semicolon-separated SQL statements.
XQuery Expression:
let $sqls := {
for $obj in //InspectObjects/Object
return {
for $att in $obj/Attachments/Attachment
return fn:concat("insert into my_table(id,attachment)values('",
xs:string($obj/@id), "','",
xs:string($att/@id), "')")
}
}
return fn:string-join($sqls, ";")
Semicolon-separated SQL Statements:
insert into my_table(id,attachment)values('AAA','a.jpg');insert into my_table(id,attachment)values('AAA','b.jpg');insert into my_table(id,attachment)values('BBB','c.jpg');insert into my_table(id,attachment)values('BBB','d.jpg')
You can then execute the statements with SQLExecutor at once ("FME_SQL_DELIMITER ;" declaration is required).
