From: | Marcin Stępnicki <mstepnicki(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | xpath_table feature |
Date: | 2009-03-13 11:34:16 |
Message-ID: | 179149fe0903130434l46cc3f2eh39924df73870c98@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello.
I've read the documentation, I think I have even found the article
after which this part of documentation has been updated
(http://markmail.org/thread/ys2a2yph5zgmnls5) but I still can't write
appropriate query :-(.
Let's consider the following:
create table test_xml (id serial primary key, xml text);
insert into test_xml (xml) values ('<?xml version="1.0" encoding="utf-8"?>
<lev1 attr1="a">
<lev2 attr2="x" attr3="y">
<lev3 attr4="3">
<lev4 attr5="aaa"></lev4>
</lev3>
</lev2>
<lev2 attr2="o" attr3="u">
<lev3 attr4="7"></lev3>
</lev2>
<lev2 attr2="l" attr3="w">
<lev3 attr4="9">
<lev4 attr5="bbb"></lev4>
</lev3>
</lev2>
</lev1>');
Please note that second /lev1/lev2/lev3 ends here and has no lev4 child node
SELECT * from xpath_table ('id',
'xml',
'test_xml',
$$/lev1/lev2/@attr2|$$
|| $$/lev1/lev2/@attr3|$$
|| $$/lev1/lev2/lev3/lev4/@attr5$$
,
'id=1') AS (
id int,
attr2 text,
attr3 text,
attr5 text
) ;
gives:
id | attr2 | attr3 | attr5
----+-------+-------+-------
1 | x | y | aaa
1 | o | u | bbb
1 | l | w |
I think I understand why this happens, that's because '|' in xpath
indicates a simple join and /lev1/lev2/lev3/lev4/@attr5 returns the
first matched node.
However, I'd like to have:
id | attr2 | attr3 | attr5
----+-------+-------+-------
1 | x | y | aaa
1 | o | u |
1 | l | w | bbb
Could you please show me the way to achieve this?
Thank you for your time
Regards,
Marcin
From | Date | Subject | |
---|---|---|---|
Next Message | seb JACK | 2009-03-13 12:49:28 | RE : [SQL] Convert text from UTF8 to ASCII |
Previous Message | Paul Dam | 2009-03-13 08:38:08 | Re: Convert text from UTF8 to ASCII |