Question

How can I merge multiple GML node values into one cell in PostGIS database

  • 20 September 2022
  • 1 reply
  • 6 views

I have an OS mastermap GML data file. I have multiple <osgb:changeHistory> tags for one particular featureID. Which is as below:

 

<osgb:changeHistory>

<osgb:changeDate>1998-07-03</osgb:changeDate>

<osgb:reasonForChange>Modified</osgb:reasonForChange>

</osgb:changeHistory>

<osgb:changeHistory>

<osgb:changeDate>1998-07-03</osgb:changeDate>

<osgb:reasonForChange>Reclassified</osgb:reasonForChange>

</osgb:changeHistory>

<osgb:changeHistory>

<osgb:changeDate>2002-08-12</osgb:changeDate>

<osgb:reasonForChange>Attributes</osgb:reasonForChange>

</osgb:changeHistory>

<osgb:changeHistory>

<osgb:changeDate>2003-10-14</osgb:changeDate>

<osgb:reasonForChange>Position</osgb:reasonForChange>

</osgb:changeHistory>

 

I am trying to read all <osgb:changeHistory> nodes and write them in PostGIS database as below (I did it with GDAL):

imageHere, I want to count the number of changeDates and put it in a single cell (in PostGIS database) separated by comma and, if possible, within a bracket. Is it possible through FME?

 

Currently all I am getting is only the last changeDate and reasonofChange value in those respective cells.

 

 

 


1 reply

Userlevel 2
Badge +17

Hi @khandker​ 

It is possible either using XQuery (eg. XMLXQueryExtractor) or a generic XML transformer along with some list transformers. If you are not already familiar with XQuery, using the second method may be easier.

 

I have attached an example workspace demonstrating both approaches and I hope it helps. Please note that I modified your GML snippet to add a root element with an osgb namespace in order to create valid XML.

Reply