Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 4DBA0118883C
	for <pgsql-sql@postgresql.org>; Tue, 17 Jul 2012 00:24:05 -0300 (ADT)
Received: from mail-yx0-f174.google.com ([209.85.213.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1SqyOK-0006az-J7
	for pgsql-sql@postgresql.org; Tue, 17 Jul 2012 03:24:04 +0000
Received: by yenl2 with SMTP id l2so5730681yen.19
	for <pgsql-sql@postgresql.org>; Mon, 16 Jul 2012 20:23:52 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:x-gm-message-state;
	bh=H+7cN7/ruk97sa+KntFJ3zEjDLCygPxhMG7+HMAubwk=;
	b=JBoWw2zqlPodRa9BMv5Z9VZl36KonJEKZP8IeVPCJQELZ6A9Pdm0tqA3lkf9E+SOLz
	m6rqrTiVXVCSxA0cidLF0kkSDA7k5/1puAYeq+BnRhGK+p+ndfTPEvXUSxm2L2VEz2UE
	KV6j53q21mxKs5ejQ68jRsq8yA67+gallduKwFblvM3HOmxnK7RjtYd2tFAPlyZy/4q1
	6Tfc7UC2UYeNJT3REgcTDsDXvcF7XwL4jwdNx3r3lAdGjoJhQV2FnpmeaGyLYmvKVqq+
	25MeExQh4PhB3LpDt9bQMZa/LjaiClF0V9NLmuBm3B9FxfRmf3x6EGJB+vlj36fzSrce
	lFeA==
Received: by 10.66.83.164 with SMTP id r4mr1878491pay.18.1342495431591;
	Mon, 16 Jul 2012 20:23:51 -0700 (PDT)
Received: from ayaki.localdomain (office1.postnewspapers.com.au.
	[150.101.171.128])
	by mx.google.com with ESMTPS id qp9sm13117141pbc.9.2012.07.16.20.23.48
	(version=SSLv3 cipher=OTHER); Mon, 16 Jul 2012 20:23:50 -0700 (PDT)
Message-ID: <5004DAC1.9000301@ringerc.id.au>
Date: Tue, 17 Jul 2012 11:23:45 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Victor Sterpu <victor@caido.ro>
CC: pgsql-sql@postgresql.org
Subject: Re: Selecting data from XML
References: <a1dc56fc-9159-4f31-9c94-9c363757f031@caido>
In-Reply-To: <a1dc56fc-9159-4f31-9c94-9c363757f031@caido>
Content-Type: multipart/alternative;
	boundary="------------000102050901090906010301"
X-Gm-Message-State: 
 ALoCoQlJCeSJ+Z1FGQ55o5cWlA/Byak5NJVsNEnwT9nKEXEQ0H7ok9tvzqsFpNeG+wYbsQhKLwoL
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201207/17
X-Sequence-Number: 36754

This is a multi-part message in MIME format.
--------------000102050901090906010301
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------000102050901090906010301
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 07/17/2012 03:56 AM, Victor Sterpu
      wrote:<br>
    </div>
    <blockquote cite="mid:a1dc56fc-9159-4f31-9c94-9c363757f031@caido"
      type="cite">
      <pre wrap="">If I have a XML like this
&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;Errors&gt;
&lt;Error  code="CLIN102" validFrom="1980-02-23"/&gt;
&lt;Error  code="CLIN103" validFrom="1980-02-23" validTo="2012-01-01"/&gt;
&lt;/Errors&gt;

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</pre>
    </blockquote>
    <br>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a
href="http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING">http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING</a><br>
    <br>
    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.<br>
    <br>
    --<br>
    Craig Ringer<br>
    <br>
  </body>
</html>

--------------000102050901090906010301--