Re: select xpath ...
От | Ross J. Reedstrom |
---|---|
Тема | Re: select xpath ... |
Дата | |
Msg-id | 20111101161753.GA21872@rice.edu обсуждение исходный текст |
Ответ на | Re: select xpath ... (Brian Sherwood <bdsher@gmail.com>) |
Список | pgsql-sql |
(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 >
В списке pgsql-sql по дате отправления:
Предыдущее
От: Jan BakuwelДата:
Сообщение: Re: Different order by behaviour depending on where clause?