Re: Proposal: XML helper functions
От | Pavel Stehule |
---|---|
Тема | Re: Proposal: XML helper functions |
Дата | |
Msg-id | 162867791001051033i22178c26h1169dcd7ec524717@mail.gmail.com обсуждение исходный текст |
Ответ на | Proposal: XML helper functions (Scott Bailey <artacus@comcast.net>) |
Ответы |
Re: Proposal: XML helper functions
(Scott Bailey <artacus@comcast.net>)
|
Список | pgsql-hackers |
2010/1/5 Scott Bailey <artacus@comcast.net>: > One of the problem with shredding XML is that it is very kludgy to get a > scalar value back from xpath. The xpath function always returns an array of > XML. So for example, to extract a numeric value you need to: > 1) use xpath to get the node > 2) get the first element of the XML array > 3) cast that to varchar > 4) cast that to numeric > > So I wrote the following function: > > CREATE OR REPLACE FUNCTION xmlvalue( > VARCHAR, > XML > ) RETURNS TEXT AS > $$ > SELECT CASE WHEN $1 ~ '@[[:alnum:]_]+$' > THEN (xpath($1, $2))[1] > WHEN $1 ~* '/text()$' > THEN (xpath($1, $2))[1] > WHEN $1 LIKE '%/' > THEN (xpath($1 || 'text()', $2))[1] > ELSE (xpath($1 || '/text()', $2))[1] > END::text; > $$ LANGUAGE 'sql' IMMUTABLE; > > It's pretty simple. It just does a check to see if you are extracting an > attribute or an element and if element, it makes sure to get the text value. > > So query that used to look like: > > SELECT CAST( > CAST( > (xpath('/foo/bar/text()', myxml))[1] > AS varchar) > AS numeric) AS bar > > now becomes: > > SELECT xmlvalue('/foo/bar/text(), myxml)::numeric AS bar > > > Second function just checks that the xpath expression finds at least one > node. > > CREATE OR REPLACE FUNCTION xmlexists( > VARCHAR, > XML > ) RETURNS BOOLEAN AS > $$ > SELECT CASE WHEN array_upper(xpath($1, $2), 1) > 0 > THEN true ELSE false END; > $$ LANGUAGE 'sql' IMMUTABLE; > > On naming, SQL/XML specifies xmlexists and xmlcast. I am for SQL/XML naming convention. Regards Pavel Stehule Latest db2 provides > xmlcast(), Oracle has equivalent extractvalue() function, MSSQL uses > xml.value(). The xmlvalue does only part of what is required by xmlcast (it > won't cast scalar to xml). > > So would these functions need to be rewritten in c in order to be accepted? > > Regards, > > Scott Bailey > > > Further reading: > > http://scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres/ > http://en.wikibooks.org/wiki/SQL_Dialects_Reference/SQL_XML > http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions052.htm#i1131042 > http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0023486.htm > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
В списке pgsql-hackers по дате отправления: