Prototype of XMLTABLE function is done

The parsing of XML is not simple in Postgres. For more complex data I prefer external procedures in PLPerlu or PLPythonu based on usage of Xmlreader. I hope so this this time will be history. With ANSI SQL XMLTABLE function a transformation of any XML document to table is simple and fast:

postgres=# SELECT * FROM xmldata;
┌──────────────────────────────────────────────────────────────────┐
│                               data                               │
╞══════════════════════════════════════════════════════════════════╡
│ <ROWS>                                                          ↵│
│ <ROW id="1">                                                    ↵│
│   <COUNTRY_ID>AU</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>Australia</COUNTRY_NAME>                        ↵│
│   <REGION_ID>3</REGION_ID>                                      ↵│
│ </ROW>                                                          ↵│
│ <ROW id="2">                                                    ↵│
│   <COUNTRY_ID>CN</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>China</COUNTRY_NAME>                            ↵│
│   <REGION_ID>3</REGION_ID>                                      ↵│
│ </ROW>                                                          ↵│
│ <ROW id="3">                                                    ↵│
│   <COUNTRY_ID>HK</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>HongKong</COUNTRY_NAME>                         ↵│
│   <REGION_ID>3</REGION_ID>                                      ↵│
│ </ROW>                                                          ↵│
│ <ROW id="4">                                                    ↵│
│   <COUNTRY_ID>IN</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>India</COUNTRY_NAME>                            ↵│
│   <REGION_ID>3</REGION_ID>                                      ↵│
│ </ROW>                                                          ↵│
│ <ROW id="5">                                                    ↵│
│   <COUNTRY_ID>JP</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>Japan</COUNTRY_NAME>                            ↵│
│   <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>↵│
│ </ROW>                                                          ↵│
│ <ROW id="6">                                                    ↵│
│   <COUNTRY_ID>SG</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>Singapore</COUNTRY_NAME>                        ↵│
│   <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>            ↵│
│ </ROW>                                                          ↵│
│ </ROWS>                                                          │
└──────────────────────────────────────────────────────────────────┘
(1 row)

postgres=# SELECT  xmltable.*
   FROM (SELECT data FROM xmldata) x,
        LATERAL xmltable('/ROWS/ROW'
                         PASSING data
                         COLUMNS id int PATH '@id',
                                  country_name text PATH 'COUNTRY_NAME',
                                  country_id text PATH 'COUNTRY_ID',
                                  region_id int PATH 'REGION_ID',
                                  size float PATH 'SIZE',
                                  unit text PATH 'SIZE/@unit',
                                  premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');

┌────┬──────────────┬────────────┬───────────┬──────┬──────┬───────────────┐
│ id │ country_name │ country_id │ region_id │ size │ unit │ premier_name  │
╞════╪══════════════╪════════════╪═══════════╪══════╪══════╪═══════════════╡
│  1 │ Australia    │ AU         │         3 │    ¤ │ ¤    │ not specified │
│  2 │ China        │ CN         │         3 │    ¤ │ ¤    │ not specified │
│  3 │ HongKong     │ HK         │         3 │    ¤ │ ¤    │ not specified │
│  4 │ India        │ IN         │         3 │    ¤ │ ¤    │ not specified │
│  5 │ Japan        │ JP         │         3 │    ¤ │ ¤    │ Sinzo Abe     │
│  6 │ Singapore    │ SG         │         3 │  791 │ km   │ not specified │
└────┴──────────────┴────────────┴───────────┴──────┴──────┴───────────────┘
(6 rows)

link to patch