Proposal: XML helper functions

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. 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