Re: XMLTABLE question

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

> Thanks for your quick response.
>
>
>
> We’ve managed to resolve this problem.
>
>
>
> 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') xt1,
>
> XMLTABLE('//items/item' PASSING xt1.ITEMS_XML COLUMNS ITEM_POS FOR
> ORDINALITY, ITEM_TEXT CHARACTER VARYING(300) PATH 'text', ITEM_VALUE
> CHARACTER VARYING(300) PATH 'value') xt2
>
> WHERE xt.id = 1;
>
>
>
> Seems to be different on the way you need to input the path through to
> another XMLTABLE than you do in Oracle – this seems to resolve my problem.
>

It is great. More, because fixing on PostgreSQL level is not simple. There
is incompatibility between Oracle and Postgres probably during different
implementation, and little bit strange behave of //.

PostgreSQL XML table for every row just set current node. Looks like Oracle
creates new document. This generates different result because // is related
to any place in document, not to current node. Another issue is
impossibility to work with balanced chunks, and requirement the XML
documents. Looks like libxml2, that is used in Postgres doesn't support it.

Looks like better to use ".//" in Postgres instead "//" in Oracle.

I found interesting article
https://www.bennadel.com/blog/2142-using-and-expressions-in-xpath-xml-search-directives-in-coldfusion.htm

so some new for me about XPath

Regards

Pavel

>
> Thanks for your time.
>
>
>
> David
>
>
>
> *From:* Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
> *Sent:* 04 September 2018 16:06
> *To:* David Day <David(dot)Day(at)cdl(dot)co(dot)uk>
> *Cc:* pgsql-sql(at)lists(dot)postgresql(dot)org; Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> *Subject:* 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
>
>
>