Re: XMLTABLE question

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: XMLTABLE question
Дата
Msg-id CAFj8pRBcvoD2MuaSkghkpyTzX_VVTNXsLd_MGHKoYApitHQTGg@mail.gmail.com
обсуждение исходный текст
Ответ на XMLTABLE question  (David Day <David.Day@cdl.co.uk>)
Ответы RE: XMLTABLE question  (David Day <David.Day@cdl.co.uk>)
Список pgsql-sql
Дерево обсуждения
Hi

2018-09-04 12:05 GMT+02:00 David Day <David.Day@cdl.co.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

 

 


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


В списке pgsql-sql по дате отправления:

Предыдущее
От: David Day
Дата:
Сообщение: XMLTABLE question
Следующее
От: David Day
Дата:
Сообщение: RE: XMLTABLE question