Formatter error with using XMLTABLE using no explicit data type

Toad for Oracle
1

Hi,

I’m getting errors formatting a query that uses XMLTABLE without using explicit data types for the definition of COLUMNS clause (since the underlying table is XML schema-based, providing data types is not neccessary).

Versions affected: Toad for Oracle Xpert 12.1.1.1, 11.6.

Example:

SELECT msgid
FROM pkp,
XMLTABLE (xmlnamespaces (DEFAULT ‘urn:iso:std:iso:20022:tech:xsd:pain.008.001.02’), ‘/Document/CstmrDrctDbtInitn/GrpHdr’
PASSING p3pkp8_xml
COLUMNS msgid PATH ‘MsgId’);

[Error] Syntax check (5: 37): ERROR line 5, col 37, ending_line 5, ending_col 43, Found ‘‘MsgId’’, Expecting: ) -or- , -or- DEFAULT -or- PATH -or- % -or- ( @ -or- .

Any help would be much appreciated.

Thanks!

2

pkp structure=?

Can we get it?

Some data maybe (you have my mail to send as well)

3

The table is structured as:

CREATE TABLE pkp(
p3pk_id NUMBER NOT NULL,
p3pkp8_xml XMLTYPE NOT NULL)
XMLTYPE COLUMN p3pkp8_xml
ELEMENT “pain.008.001.02#Document”;

XML schema is available on: www.iso20022.org/…/pain.008.001.02.zip

I guess data is not relevant, since the query runs perfectly, it’s just that Formatter doesn’t like it.

4

How is data relevant for code formatting?

5

This looks like a bug. We’ll fix is asap. (QP-428)

Thanks for reporting.

6

How is data relevant for code formatting?

Sorry thought it was a problem with SQL.

Glad to have confirmation as a bug.

7

Hi Andre, can you please check if QP-428 will be included in Toad 12.5?

Thanks!

8

It has been fixed in the relevant component and it’s on its way to Toad, so it should be part of 12.5. But of course the Toad guys have the last word on it…