Skip site navigation (1) Skip section navigation (2)

Using xpath queries against XML Datatype

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 (view raw or flat)
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.

Responses

pgsql-novice by date

Next:From: Jasen BettsDate: 2010-02-11 11:31:04
Subject: Re: Novice PL/pgSQL question and example
Previous:From: A. KretschmerDate: 2010-02-11 07:06:55
Subject: Re: Postgres Triggers issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group