On 10/29/18 6:40 AM, Thomas Kellerer wrote:
> That line seems to be valid - but you need to pass an XMLTYPE value,
> not a VARCHAR
>
> https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=21cdf890a26e97fa8667b2d6a960bd33
Oh, of course! Thank you. I had forgotten pass the context item
as explicitly an XML value.
That illustrates that, in a proper XMLTABLE, you can pass things
that are not XML values. When a varchar is passed in, the context
item has type xs:string. The third PATH tried to follow a node path
against a non-node context item, and correctly reported the error.
And thanks for the dbfiddle pointer. I can now confirm (in both 11g.2
and 18c):
SELECT *
FROM XMLTABLE('.'
PASSING xmltype('<sale hatsize="7" customer="alice" taxable="false"/>')
COLUMNS
a varchar(10) PATH '"cat" < "dog"',
b varchar(10) PATH '"cat" > "dog"',
c varchar(10) PATH 'sale/@taxable = false()'
);
A B C
true false true
Or as numbers (There's just no SQL boolean type in Oracle, even 18g!):
SELECT *
FROM XMLTABLE('.'
PASSING xmltype('<sale hatsize="7" customer="alice" taxable="false"/>')
COLUMNS
a NUMBER PATH '"cat" < "dog"',
b NUMBER PATH '"cat" > "dog"',
c NUMBER PATH 'sale/@taxable = false()'
);
A B C
1 0 1
I removed the string() wrappings, which were only to allow the
same query to work in PG, but Pavel's proposed patches will make
them unnecessary.
Note, however, that the first proposed patch will work for the
first query (varchar results) and fail for the second (number
results). The second patch will work for the second query, but
produce the wrong strings ("1" or "0" instead of "true" or "false")
for the first. A proper XMLTABLE needs to apply the appropriate
conversion determined by the SQL type of the output column.
I believe a patch to do that correctly is possible; xml.c has
access to the type oids for the output columns, after all.
The fact that PG will return false|false|false or 0|0|0 instead
of true|false|true or 1|0|1 cannot be fixed by a patch. That is
the consequence of evaluating in XPath 1.0 (in XPath 2.0, which is
a subset of XQuery, the results would be correct).
On the same lines, we can take my original example where I forgot
to type the context item as XML, and make that work in Oracle too:
SELECT *
FROM XMLTABLE('.'
PASSING '<sale hatsize="7" customer="alice" taxable="false"/>'
COLUMNS
a varchar(10) PATH 'substring-after(., "taxable=")'
);
A
"false"/>
A proper XMLTABLE is happy to be passed an atomic value, such as
a string, as the context item or any named parameter, and apply
type-appropriate operators and functions to it. XPath 1.0 blocks
that for PG.
-Chap
