Processing GML data using PostgreSQL’s XMLTABLE

XMLTABLE was added to PostgreSQL at version 10.

All examples of how to process spatial XML on this site for PostgreSQL/PostGIS were based on 9.x.

Here is an example of how to process GML using XMLTABLE.

With hoteldata as (
SELECT '<hotels>
  <hotel id="mancha">
  <name>La Mancha</name>
  <location>
    <Point srsName="EPSG:4326">
       <pos>-43.23737 147.14756</pos>
    </Point>
  </location>
  <rooms>
   <room id="201">
     <capacity>3</capacity>
     <comment>Great view of the Channel</comment>
     <location>
      <Point srsName="EPSG:4326">
        <pos>-43.23731 147.14751</pos>
      </Point>
     </location>
  </room>
   <room id="202">
     <capacity>5</capacity>
     <location>
      <Point srsName="EPSG:4326">
        <pos>-43.23732 147.14758</pos>
      </Point>
     </location>
    </room>
  </rooms>
 </hotel>
 <hotel id="valpo">
  <name>Valparaíso</name>
  <location>
    <Point srsName="EPSG:4326">
       <pos>-43.26737 147.29756</pos>
    </Point>
  </location>
  <rooms>
   <room id="201">
     <capacity>2</capacity>
     <comment>Very noisy</comment>
     <location>
       <Point srsName="EPSG:4326">
          <pos>-43.26729 147.29750</pos>
       </Point>
     </location>
   </room>
   <room id="202">
     <capacity>2</capacity>
     <location>
       <Point srsName="EPSG:4326">
          <pos>-43.26741 147.29734</pos>
       </Point>
     </location>
   </room>
   <room id="203">
     <capacity>4</capacity>
     <comment>Very comfortable</comment>
     <location>
       <Point srsName="EPSG:4326">
          <pos>-43.26740 147.29760</pos>
       </Point>
     </location>
   </room>
  </rooms>
 </hotel>
</hotels>'::xml as hotels
)
SELECT decoded.room_id, 
       decoded.capacity, 
       decoded.comment,
       ST_AsEWKT(
          ST_GeomFromGML(decoded.location::text,4326)
       ) as geom
FROM
    hoteldata
    cross join
    xmltable(
        '//hotels/hotel/rooms/room'
        passing hotels
        COLUMNS
            room_id  varchar(3) path '@id',
            location xml        path 'location/Point',
            capacity integer,
            comment  text
    ) AS decoded;

The result is as follows:

I hope this is useful to someone.