Re: patch: function xmltable

Alvaro Herrera wrote:

> If you use "PATH '/'" for a column, you get the text for all the entries
> in the whole XML, rather than the text for the particular row being
> processed. Isn't that rather weird, or to put it differently, completely
> wrong? I didn't find a way to obtain the whole XML row when you have
> the COLUMNS option (which is what I was hoping for with the "PATH '/'").

Ah, apparently you need to use type XML for that column in order for
this to happen. Example:

insert into emp values ($$
<depts >
<dept bldg="102">
<employee id="905">
<name>
<first>John</first>
<last>Doew</last>
</name>
<office>344</office>
<salary currency="USD">55000</salary>
</employee>

<employee id="908">
<name>
<first>Peter</first>
<last>Panw</last>
</name>
<office>216</office>
<phone>905-416-5004</phone>
</employee>
</dept>

<dept bldg="115">
<employee id="909">
<name>
<first>Mary</first>
<last>Jonesw</last>
</name>
<office>415</office>
<phone>905-403-6112</phone>
<phone>647-504-4546</phone>
<salary currency="USD">64000</salary>
</employee>
</dept>
</depts>
$$);

Note the weird salary_amount value here:

SELECT x.*
FROM emp,
XMLTABLE ('//depts/dept/employee' passing doc
COLUMNS
i for ordinality,
empID int PATH '@id',
firstname varchar(25) PATH 'name/first' default 'FOOBAR',
lastname VARCHAR(25) PATH 'name/last',
salary xml path 'concat(salary/text(), salary/@currency)' default 'DONT KNOW', salary_amount xml path '/' )
WITH ORDINALITY
AS X (i, a, b, c) limit 1;
i │ a │ b │ c │ salary │ salary_amount │ ordinality
───┼─────┼──────┼──────┼──────────┼───────────────────────┼────────────
1 │ 905 │ John │ Doew │ 55000USD │ ↵│ 1
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ John ↵│
│ │ │ │ │ Doew ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ 344 ↵│
│ │ │ │ │ 55000 ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ Peter ↵│
│ │ │ │ │ Panw ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ 216 ↵│
│ │ │ │ │ 905-416-5004↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ Mary ↵│
│ │ │ │ │ Jonesw ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ 415 ↵│
│ │ │ │ │ 905-403-6112↵│
│ │ │ │ │ 647-504-4546↵│
│ │ │ │ │ 64000 ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ ↵│
│ │ │ │ │ │
(1 fila)

If you declare salary_amount to be text instead, it doesn't happen anymore.
Apparently if you put it in a namespace, it doesn't hapen either.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services