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;
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
