From boris@localhost.localdomain Sat Sep 17 18:00:04 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1C783B5DC2D for ; Sat, 17 Sep 2011 15:00:04 -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 77411-04 for ; Sat, 17 Sep 2011 17:59:57 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 15EC5B5DC24 for ; Sat, 17 Sep 2011 14:59:57 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.4/8.14.4) with ESMTP id p8HHxuBc092307 for ; Sat, 17 Sep 2011 14:59:56 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.4/8.14.4/Submit) id p8HHkmwY071445 for pgsql-sql@postgresql.org; Sat, 17 Sep 2011 14:46:48 -0300 (ADT) (envelope-from news) From: boris X-Newsgroups: pgsql.sql Subject: select xpath ... Date: Sat, 17 Sep 2011 13:48:03 -0400 Organization: Aioe.org NNTP Server Lines: 34 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@aioe.org User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.16) Gecko/20110818 Icedove/3.0.11 X-Notice: Filtered by postfilter v. 0.8.2 To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.296 tagged_above=-5 required=5 tests=BAYES_50=0.8, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201109/49 X-Sequence-Number: 35849 hi all, I've inserted xml file : zzzzzz ...................... 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. From robjsargent@gmail.com Mon Sep 19 14:49:31 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 00065B5DC02 for ; Mon, 19 Sep 2011 11:49:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 77843-07 for ; Mon, 19 Sep 2011 14:49:25 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from out02.mta.xmission.com (out02.mta.xmission.com [166.70.13.232]) by mail.postgresql.org (Postfix) with ESMTP id 4BC8CB5DC01 for ; Mon, 19 Sep 2011 11:49:25 -0300 (ADT) Received: from in01.mta.xmission.com ([166.70.13.51]) by out02.mta.xmission.com with esmtps (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1R5f9w-00022o-9F; Mon, 19 Sep 2011 08:49:24 -0600 Received: from [208.187.158.94] (helo=[172.16.1.26]) by in01.mta.xmission.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1R5f9v-0007H5-Qi; Mon, 19 Sep 2011 08:49:24 -0600 Message-ID: <4E775670.9090208@gmail.com> Date: Mon, 19 Sep 2011 08:49:20 -0600 From: Rob Sargent User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18) Gecko/20110616 SUSE/3.1.11 Lightning/1.0b2 Thunderbird/3.1.11 MIME-Version: 1.0 To: pgsql-sql@postgresql.org References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-XM-SPF: eid=; ; ; mid=; ; ; hst=in01.mta.xmission.com; ; ; ip=208.187.158.94; ; ; frm=robjsargent@gmail.com; ; ; spf=neutral X-XM-AID: U2FsdGVkX18ZJNardmN7b3UW0vEo3FZRapEBpTLjy7M= X-SA-Exim-Connect-IP: 208.187.158.94 X-SA-Exim-Mail-From: robjsargent@gmail.com X-Spam-DCC: XMission; sa03 1397; Body=1 Fuz1=1 Fuz2=1 X-Spam-Combo: ;pgsql-sql@postgresql.org X-Spam-Relay-Country: Subject: Re: select xpath ... X-SA-Exim-Version: 4.2.1 (built Fri, 06 Aug 2010 16:31:04 -0600) X-SA-Exim-Scanned: Yes (on in01.mta.xmission.com) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.889 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201109/51 X-Sequence-Number: 35851 Having a name space in the doc requires it's usage in the query. On 09/17/2011 11:48 AM, boris wrote: > hi all, > I've inserted xml file : > > > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> > > zzzzzz > ...................... > > > 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. > > > > From boris@localhost.localdomain Mon Sep 19 16:00:12 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 25317B5DBF2 for ; Mon, 19 Sep 2011 13:00:12 -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 15398-03 for ; Mon, 19 Sep 2011 16:00:01 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 475ADB5DBC4 for ; Mon, 19 Sep 2011 13:00:01 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.4/8.14.4) with ESMTP id p8JG00If020615 for ; Mon, 19 Sep 2011 13:00:00 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.4/8.14.4/Submit) id p8JFllOE097528 for pgsql-sql@postgresql.org; Mon, 19 Sep 2011 12:47:47 -0300 (ADT) (envelope-from news) From: boris X-Newsgroups: pgsql.sql Subject: Re: select xpath ... Date: Mon, 19 Sep 2011 11:49:03 -0400 Organization: Aioe.org NNTP Server Lines: 47 Message-ID: References: <4E775670.9090208@gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@aioe.org User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.16) Gecko/20110818 Icedove/3.0.11 X-Notice: Filtered by postfilter v. 0.8.2 To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.505 tagged_above=-5 required=5 tests=BAYES_40=-0.001, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201109/53 X-Sequence-Number: 35853 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 : >> >> >> > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> >> >> zzzzzz >> ...................... >> >> >> 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. >> >> >> >> > From bdsher@gmail.com Wed Sep 21 16:27:48 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D408FB5DC0A for ; Wed, 21 Sep 2011 13:27:48 -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 99865-04 for ; Wed, 21 Sep 2011 16:27:42 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com [74.125.82.174]) by mail.postgresql.org (Postfix) with ESMTP id 36F16B5DC09 for ; Wed, 21 Sep 2011 13:27:42 -0300 (ADT) Received: by wyg34 with SMTP id 34so2077987wyg.19 for ; Wed, 21 Sep 2011 09:27:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type:content-transfer-encoding; bh=Y9vetol/NKmuTTDCC199/JzbywGN/8lsH0xvrAuFTeI=; b=uqAMnrUZ0fn1JhAbZd5D0hzov1rolAm01gDgTdn4p7IbG10c6GuTugQZTFH/41+QUu D/k42xTgpKvk4qqEZUNJ1ThZ/RKCqO8utV4C2D99EKb0Nce2heObFrBKLcJuqFGfuPV4 X6c88Yh7kY/7dgMbk/0Tjd1BrNfZYipmRPj1s= MIME-Version: 1.0 Received: by 10.227.8.196 with SMTP id i4mr967623wbi.41.1316622461369; Wed, 21 Sep 2011 09:27:41 -0700 (PDT) Received: by 10.227.117.4 with HTTP; Wed, 21 Sep 2011 09:27:41 -0700 (PDT) In-Reply-To: References: <4E775670.9090208@gmail.com> Date: Wed, 21 Sep 2011 12:27:41 -0400 Message-ID: Subject: Re: select xpath ... From: Brian Sherwood To: pgsql-sql@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.889 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201109/59 X-Sequence-Number: 35859 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 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 : >>> >>> >>> >> xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance"> >>> =A0 =A0 >>> =A0 =A0 zzzzzz >>> ...................... >>> >>> >>> to a table: >>> >>> CREATE TABLE "temp".tempxml >>> ( >>> =A0 record xml >>> ) >>> >>> I can get it using: >>> select * from temp.tempxml >>> >>> >>> but, I can't get any values using xpath. ex: >>> >>> >>> =A0 select (xpath('/document/title/text()', record ))[1] from temp.temp= xml >>> >>> >>> 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 > From reedstrm@rice.edu Wed Nov 2 00:08:52 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 4D9D0B5DE95 for ; 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 ; 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 ; 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 ; Tue, 1 Nov 2011 18:03:59 -0300 (ADT) Received: from reedstrm by cooker.cnx.rice.edu with local (Exim 4.72) (envelope-from ) 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" To: Brian Sherwood Cc: pgsql-sql@postgresql.org Subject: Re: select xpath ... Message-ID: <20111101161753.GA21872@rice.edu> References: <4E775670.9090208@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: 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 ('       zzzzzz ' ); select * from tempxml ; record --------------------------------------------------------       zzzzzz (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 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 : > >>> > >>> > >>> >>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> > >>>     > >>>     zzzzzz > >>> ...................... > >>> > >>> > >>> 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 >