xpath_table feature
От | Marcin Stępnicki |
---|---|
Тема | xpath_table feature |
Дата | |
Msg-id | 179149fe0903130434l46cc3f2eh39924df73870c98@mail.gmail.com обсуждение исходный текст |
Список | 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
В списке pgsql-sql по дате отправления: