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

Re: Selecting data from XML

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: Victor Sterpu <victor(at)caido(dot)ro>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Selecting data from XML
Date: 2012-07-17 03:23:45
Message-ID: 5004DAC1.9000301@ringerc.id.au (view raw or flat)
Thread:
Lists: pgsql-sql
On 07/17/2012 03:56 AM, Victor Sterpu wrote:
> If I have a XML like this
> <?xml version="1.0" encoding="UTF-8"?>
> <Errors>
> <Error  code="CLIN102" validFrom="1980-02-23"/>
> <Error  code="CLIN103" validFrom="1980-02-23" validTo="2012-01-01"/>
> </Errors>
>
> can I write a query that will output the columns names and values like this?
>
> code;validFrom;validTo
> ------------------------------
> CLIN102;1980-02-23;
> CLIN103;1980-02-23;2012-01-01

http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING

You should be able to do it with some xpath expressions. It probably 
won't be fast or pretty. Consider using PL/Python, PL/perl, PL/Java, or 
something like that to do the processing and return the resultset.

--
Craig Ringer

In response to

pgsql-sql by date

Next:From: Puneet MishraDate: 2012-07-18 06:48:13
Subject: Order preservation of search phrases in postgresql FTS/OpenFTS
Previous:From: Victor SterpuDate: 2012-07-16 19:56:27
Subject: Selecting data from XML

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