2010/1/5 Scott Bailey <arta...@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
>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
