Question

XML Array to individual SQL inserts - one record per array item

  • 27 February 2018
  • 4 replies
  • 10 views
B
Badge +1

I am searching for a method to take an XML based array and insert each array value into a new record. Currently my workspace parses non-array XML tags\\variable and inserts them into one single SQL record, but now I need a one to many relationship. The number of array items is indefinite.

 

 

The use case is that we get field inspections in the form of an XML document that gets inserted into our SQL database (various tables - all 1:1). We now want to start registering file\\photo attachments to a reference table, however the photos are stored in an array (not a simple 1:1 insert).

 

 

Has anyone accomplished this? Idea would be much appreciated. Thanks!

4 replies

takashi
Rank
Userlevel 2
Badge +17

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

B
Badge +1

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:)

 

 

takashi
Rank
Userlevel 2
Badge +17

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)

 

 

takashi
Rank
Userlevel 2
Badge +17

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).

 

Reply


Community Stats

28,727
Posts
109,522
Replies
36,566
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.