[SQL] xpath_table, or something similair?

[Mario Splivalo]

Is there a way to get recordset out of XML document - something similair 
to contrib/xml2's xpath_table function, but I'd like to be able to get 
data out of XML document that is not stored in the database.


I wrote my own plpgsql function for converting XML document data to 
recordset (table), but that requires more than few xpath/generate_series 
functions, and I think it might get slow for rather large documents. Now 
I'm wondering if that is the only/right way, or there are some other 
tools I might not be aware of.


Thanks in advance,

Mike

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] xpath_table feature

[Marcin Stępnicki]

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 ('











   


');


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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] xpath_table

[Thomas Kellerer]

Hi, 

I am using xpath_table to convert elements from an XML column to "rows". 

Now according to 
http://www.postgresql.org/docs/8.3/static/xml2.html
this function will be removed in a future version. 


That chapter also claims that the new XML syntax covers the functionality of 
the xml2 module, but I cannot find a way to return the elements of an XML 
document as rows (as xpath_table does)

Suppose I have the following content in my xml column:


 
 



I am using a statement similar to this:

select member_id, member_name
from xpath_table('id', 'xml_text', 'xmltest', 
'/team/member/@id|/team/member/@name', 'true')
as t(id integer, member_id varchar, member_name varchar)

to get the following output

member_id   member_name
10  Arthur Dent
11  Ford Prefect

How would I achieve the same without using the deprecated xml2 module?

Thanks in advance
Thomas


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql