Proposal: XML helper functions

From: Scott Bailey <artacus(at)comcast(dot)net>
To: hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Proposal: XML helper functions
Date: 2010-01-05 18:14:12
Message-ID: 4B438174.8050004@comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2010-01-05 18:18:28 Re: Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Previous Message Robert Haas 2010-01-05 18:11:05 Re: Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial