Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 4D9D0B5DE95
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  1 Nov 2011 21:08:52 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 00961-01-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  2 Nov 2011 00:08:41 +0000 (UTC)
Received: from mx2.hub.org (mx2.hub.org [200.46.204.254])
	by mail.postgresql.org (Postfix) with ESMTP id 0C7CCB5E0BB
	for <pgsql-sql@postgresql.org>; Tue,  1 Nov 2011 21:05:15 -0300 (ADT)
X-Greylist: delayed 04:44:58.200608 by SQLgrey-1.8.0-rc2
Received: from cooker.cnx.rice.edu (cooker.cnx.rice.edu [168.7.5.70])
	by mx2.hub.org (Postfix) with ESMTP id 37A8E17539EF
	for <pgsql-sql@postgresql.org>; Tue,  1 Nov 2011 18:03:59 -0300 (ADT)
Received: from reedstrm by cooker.cnx.rice.edu with local (Exim 4.72)
	(envelope-from <reedstrm@rice.edu>)
	id 1RLH29-0008RF-Em; Tue, 01 Nov 2011 11:17:53 -0500
Date: Tue, 1 Nov 2011 11:17:53 -0500
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Brian Sherwood <bdsher@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: select xpath ...
Message-ID: <20111101161753.GA21872@rice.edu>
References: <j52me3$ehq$1@speranza.aioe.org> <4E775670.9090208@gmail.com>
	<j57o6v$67k$2@speranza.aioe.org>
	<CAExyqyj=rvONPejpDghQdoJ2qne-OtBZfszVYLzMUYfDpGW1qQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAExyqyj=rvONPejpDghQdoJ2qne-OtBZfszVYLzMUYfDpGW1qQ@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-06-14)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.308 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	DATE_IN_PAST_03_06=1.592
X-Spam-Level: 
X-Archive-Number: 201111/1
X-Sequence-Number: 35974

(Note: catching up on a severe list backlog, thought I'd complete this
thread for the archives)

Brian - 
In case Boris never sent anything directly, I'll extend his example and
show a solution. The usual problem w/ namespaces is getting your head
wrapped around the fact that they're local aliases: the fully expanded form of
each tag name is what any XSL actually operates on.  This is convenient, since
while XML documents allow you to define a default (anonymous) namespace, XSL
does not. But since matching is done on the namespace value, not the alias, you
can work around that by using an explicit alias in the XSL.

The postgresql xpath() function takes a third argument, which is an ARRAY of
ARRAYs of namespace aliases. Since 

So, w/ Boris's example:

CREATE temp TABLE tempxml ( record xml);

insert into tempxml values ('<?xml version="1.0"?>
<document xmlns:s1="urn:myorg/s1" xmlns="urn:myorg"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <id num="111-222-333-4444"/>
   <title>zzzzzz</title>
 </document>'
 );

select * from tempxml ;
                            record                         
 --------------------------------------------------------
  <document xmlns:s1="urn:myorg/s1"
  xmlns="urn:myorg"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <id num="111-222-333-4444"/>
        <title>zzzzzz</title>
       </document>
       (1 row)

No namespaces retrieves nothing:

select (xpath('document/title/text()', record))[1] from tempxml; xpath 
-------
 
(1 row)

Correct namespace (on all the tags):

select (xpath('/my:document/my:title/text()', record, ARRAY[ARRAY['my','urn:myorg']]))[1] from tempxml;
 xpath  
--------
 zzzzzz

Attempt to use a 'default' namespace:

select (xpath('/document/title/text()', record, ARRAY[ARRAY['','urn:myorg']]))[1] from tempxml;
ERROR:  could not register XML namespace with name "" and URI "urn:myorg"

Hope that helps,
Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist        phone: 713-348-6166
Connexions                  http://cnx.org            fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE
On Wed, Sep 21, 2011 at 12:27:41PM -0400, Brian Sherwood wrote:
> Boris,
> 
> Can you send me your final solution?
> I am trying to do something similar and I think I am stuck at the namespace.
> 
> Thanks
> 
> 
> On Mon, Sep 19, 2011 at 11:49 AM, boris <boris@localhost.localdomain> wrote:
> > On 09/19/2011 10:49 AM, Rob Sargent wrote:
> >>
> >> Having a name space in the doc requires it's usage in the query.
> >
> > yeah, I got it... I was using wrong one...
> > thanks.
> >
> >
> >>
> >>
> >> On 09/17/2011 11:48 AM, boris wrote:
> >>>
> >>> hi all,
> >>> I've inserted xml file :
> >>>
> >>> <?xml version="1.0"?>
> >>> <document xmlns:s1="urn:myorg/s1" xmlns="urn:myorg"
> >>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> >>>     <id num="111-222-333-4444"/>
> >>>     <title>zzzzzz</title>
> >>> ......................
> >>>
> >>>
> >>> to a table:
> >>>
> >>> CREATE TABLE "temp".tempxml
> >>> (
> >>>   record xml
> >>> )
> >>>
> >>> I can get it using:
> >>> select * from temp.tempxml
> >>>
> >>>
> >>> but, I can't get any values using xpath. ex:
> >>>
> >>>
> >>>   select (xpath('/document/title/text()', record ))[1] from temp.tempxml
> >>>
> >>>
> >>> am I doing it right?
> >>>
> >>> thanks.
> >>>
> >>>
> >>>
> >>>
> >>
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> >
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>