[postgis-users] Has anyone tried GML to table?

[Simon G Greener]

I've implemented XML processing of spatial data within the database a 
number of times.

This includes Oracle, SQL Server + Spatial and PostgreSQL/PostGIS.

See my website for examples.

Regina has some examples as well.

Here is an example of how to use XMLTABLE to process a modified version 
of the data at the URL you pasted.

Hope it Helps.

regards

Simon

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.location::text,
        decoded.capacity,
        decoded.comment,
        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',
             capacity integer,
             comment  text
     ) AS decoded;