Pavel Stehule wrote:
> 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.
Well I'm shying away from the name xmlcast because it is supposed to
cast xml to scalar, scalar to xml and xml to xml. For instance these
would all work on db2.
SELECT xmlcast(null AS XML),
xmlcast(bar AS XML),
xmlcast(xmlquery('$x/baz/bar' PASSING foo.barxml AS x) AS VARCHAR(30))
FROM foo
But I just found that Oracle added xmlcast in 11g and it only does xml
to scalar (and only number, varchar2 and date/time types). So maybe
you're right.
Scott