Thread: Using xpath queries against XML Datatype

Using xpath queries against XML Datatype

From

"Balma Robin Gordon"

Date:

11 February 2010, 08:50:21

Hello,

I have a XML document stored in a PostgreSQL column defined using a XML datatype. A fragment of this document is included below:

----------------------------------------------------------------------------

<sml:SensorML xmlns:sml="http://www.opengis.net/sensorML/1.0.1"

xmlns:swe="http://www.opengis.net/swe" xmlns:gml="http://www.opengis.net/gml"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:xlink="http://www.w3.org/1999/xlink" version="1.0.1">

<sml:member>

<sml:System gml:id="CTD1454">

<gml:description>M1 deployment of 10m CTD (Model SBE 37-SMP) with serial communication, internal memory and pump. The SBE 37-SMP MicroCAT is a high-accuracy conductivity and temperature (pressure optional) recorder with internal battery and memory. Designed for moorings or other long duration, fixed-site deployments, the MicroCAT includes a standard serial interface and nonvolatile FLASH memory. Construction is of titanium and other non-corroding materials to ensure long life with minimum maintenance, and depth capability is 7000 meters (23,000 feet).</gml:description>

<sml:keywords>

<sml:KeywordList

codeSpace="http://gcmd.nasa.gov/Resources/valids/keyword_list.html">

<sml:keyword>MBARI</sml:keyword>

<sml:keyword>OCEANOGRAPHY</sml:keyword>

....

-----------------------------------------------------------------------------

I would like to extract <gml:description> where gml:id="CTD1454". In SQL Server

2008 I write the following query:

WITH XMLNAMESPACES ('http://www.opengis.net/sensorML/1.0.1' AS sml,

'http://www.opengis.net/swe' AS swe,

'http://www.opengis.net/gml' AS gml

)

SELECT

sensorML_xml_u.query('data(//sml:System[@gml:id="CTD1454"]/gml:description)')

FROM dbo.sensorML

How do I do this in PostgreSQL? I'm struggling trying to find any documentation/examples. The following does not appear to return anything, but I'm not sure if I have the syntax quite right:

select * from sensorML WHERE (xpath('//sml:System/@gml:id', "sensorML_xml", ARRAY[ARRAY['sml','http://www.opengis.net/sensorML/1.0.1'],ARRAY['gml','http://www.opengis.net/sensorML/1.0.1']]))::text

= 'CTD1454';

Thanks,

Robin

This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error. QinetiQ may monitor email traffic data and also the content of email for the purposes of security. QinetiQ Limited (Registered in England & Wales: Company Number: 3796233) Registered office: 85 Buckingham Gate, London SW1E 6PD http://www.qinetiq.com

Re: Using xpath queries against XML Datatype

From

Tim Landscheidt

Date:

11 February 2010, 17:20:18

"Balma Robin Gordon" <RGBALMA@qinetiq.com> wrote:

> [...]
> How do I do this in PostgreSQL?  I'm struggling trying to find any
> documentation/examples.  The following does not appear to return
> anything, but I'm not sure if I have the syntax quite right:

> select * from sensorML WHERE (xpath('//sml:System/@gml:id',
> "sensorML_xml",
> ARRAY[ARRAY['sml','http://www.opengis.net/sensorML/1.0.1'],ARRAY['gml','
> http://www.opengis.net/sensorML/1.0.1']]))::text

> = 'CTD1454';

xpath () returns an /array/ of XML values; so you have to
either compare the result to an array or just compare the
first element:

| SELECT * FROM sensorML WHERE ((xpath('//sml:System/@gml:id',
|                                      sensorML_xml,
|                                      ARRAY [ARRAY ['sml', 'http://www.opengis.net/sensorML/1.0.1'],
|                                             ARRAY ['gml', 'http://www.opengis.net/gml']])) [1])::TEXT = 'CTD1454';

Tim