Re: XMLTABLE question

Hi

2018-09-04 12:05 GMT+02:00 David Day <David(dot)Day(at)cdl(dot)co(dot)uk>:

> Hi,
>
>
>
> I was hoping to get some advice and potentially a solution to my problem.
>
>
>
> I have put a test case together as per attachment with the error I am
> getting when using the XMLTABLE function.
>
>
>
> I am trying to migrate my code from Oracle to Postgresql so just wondering
> how best to do this using this XMLTABLE option for this particular scenario.
>

It looks so this scenario is supported. This moment I cannot to say, if it
is a PostgreSQL bug. Now, PostgreSQL XPath function doesn't requires
document format of XML

so <a><b></b><b></b></a> is correct, <b></b><b></b> is not correct.
Theoretically it can be controlled by xmloption configuration value, but it
is not applied in this case.

I found workaround

CREATE OR REPLACE FUNCTION todoc(xml) RETURNS xml AS $$ select
xmlelement(node aux, $1) $$ language sql;

This query is working

SELECT xt.id,
xt1.RESULT_POS,
xt1.product,
xt1.name,
xt2.item_pos,
xt2.item_text,
xt2.item_value
FROM XML_TABLE xt,
XMLTABLE('//storedresults/result'
PASSING xt.resultxml
COLUMNS RESULT_POS FOR ORDINALITY,
PRODUCT CHARACTER VARYING(20) path 'product',
NAME CHARACTER VARYING(20) path 'name',
ITEMS_XML XML PATH '//items/item') xt1,
todoc(items_xml),
XMLTABLE('/aux/item'
PASSING todoc
COLUMNS
ITEM_POS FOR ORDINALITY,
ITEM_TEXT CHARACTER VARYING(300) PATH 'text',
ITEM_VALUE CHARACTER VARYING(300) PATH 'value') xt2
WHERE xt.id = 1;

Please, try to report this issue as bug

Regards

Pavel Stehule

>
>
> Kind regards
>
>
>
> David Day
>
> *Oracle Developer*
>
> CDL
>
>
>
> <http://www.cdl.co.uk/>
>
>
>
> <http://twitter.com/CDL_Software> <http://www.facebook.com/CDL-Software>
> <http://www.linkedin.com/company/cdl-cheshire-datasystems-ltd->
>
>
> Please consider the environment - Do you really need to print this email?
>
> This email is intended only for the person(s) named above and may contain
> private and confidential information. If it has come to you in error,
> please destroy and permanently delete any copy in your possession, and
> contact us on +44 (0)161 480 4420. The information in this email is
> copyright © CDL Group Holdings Limited. We cannot accept liability for any
> loss or damage sustained as a result of software viruses. It is your
> responsibility to carry out such virus checking as is necessary before
> opening any attachment.
>
> Cheshire Datasystems Limited uses software which automatically screens
> incoming emails for inappropriate content and attachments. If the software
> identifies such content or attachment, the email will be forwarded to our
> Technology department for checking. You should be aware that any email that
> you send to Cheshire Datasystems Limited is subject to this procedure.
> ------------------------------
>
> *Cheshire Datasystems Limited, Strata House, Kings Reach Road, Stockport,
> SK4 2HD*
> Registered in England and Wales with company number 3991057
> VAT registration: 727 1188 33
>