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