From: | "Balma Robin Gordon" <RGBALMA(at)qinetiq(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Using xpath queries against XML Datatype |
Date: | 2010-02-11 09:43:26 |
Message-ID: | 20100211095008.B63596327C9@mail.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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[(at)gml:id="CTD1454"]/gml:descripti
on)')
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2010-02-11 11:31:04 | Re: Novice PL/pgSQL question and example |
Previous Message | A. Kretschmer | 2010-02-11 07:06:55 | Re: Postgres Triggers issue |