Postgres Pro
Downloads
Home   >   mailing lists

Thread: Syntax of xmltable function

Syntax of xmltable function

From
Pavel Stehule
Date:
Hi

I am working on xmltable function. This function is used for simple data extraction from XML documents. In this moment I am working on prototype's parser.

The SQL/XML syntax is:

XMLTABLE (
  xmlquery
  PASSING xmldocument
  [COLUMNS columns_list] )

columns_list := column_list ',' colname

colname := colname datatype [DEFAULT value] [PATH text]

The DEFAULT clause is before PATH clause.

I am able to implement the default clause with c_expr - It is enough for almost all usage.

What is question?

A Oracle db uses little bit different syntax - the DEFAULT and PATH clause are in different order. This is little bit better for Postgres too - now I can use a_expr in DEFAULT clause - what can be little bit better for other than text types.

What should be preferrable? ANSI SQL syntax or Oracle syntax? This is not often task - I didn't find any example with DEFAULT clause for Oracle.

DB2 and Sybase uses exact ANSI SQL syntax.

Regards

Pavel

Re: Syntax of xmltable function

From
Tom Lane
Date:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> The SQL/XML syntax is:

> XMLTABLE (
>   xmlquery
>   PASSING xmldocument
>   [COLUMNS columns_list] )

> columns_list := column_list ',' colname

> colname := colname datatype [DEFAULT value] [PATH text]

> The DEFAULT clause is before PATH clause.

> A Oracle db uses little bit different syntax - the DEFAULT and PATH clause
> are in different order. This is little bit better for Postgres too - now I
> can use a_expr in DEFAULT clause - what can be little bit better for other
> than text types.

> What should be preferrable? ANSI SQL syntax or Oracle syntax? This is not
> often task - I didn't find any example with DEFAULT clause for Oracle.

ISTM that the ordering of those two clauses is totally arbitrary, and
that if you're going to worry about Oracle at all, it'd be best to allow
both orderings.
        regards, tom lane

Re: Syntax of xmltable function

From
Pavel Stehule
Date:


2016-07-31 17:53 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> The SQL/XML syntax is:

> XMLTABLE (
>   xmlquery
>   PASSING xmldocument
>   [COLUMNS columns_list] )

> columns_list := column_list ',' colname

> colname := colname datatype [DEFAULT value] [PATH text]

> The DEFAULT clause is before PATH clause.

> A Oracle db uses little bit different syntax - the DEFAULT and PATH clause
> are in different order. This is little bit better for Postgres too - now I
> can use a_expr in DEFAULT clause - what can be little bit better for other
> than text types.

> What should be preferrable? ANSI SQL syntax or Oracle syntax? This is not
> often task - I didn't find any example with DEFAULT clause for Oracle.

ISTM that the ordering of those two clauses is totally arbitrary, and
that if you're going to worry about Oracle at all, it'd be best to allow
both orderings.

ok. I checked it - and it is possible.

Regards

Pavel

                        regards, tom lane