Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14

XMLTABLE

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Some dialects ship with a built-in standard SQL table-valued function called XMLTABLE, which can be used to unnest an XML data structure into a SQL table.

SELECT *
FROM xmltable('//row'
  PASSING
    '<rows>
       <row><a>5</a><b><x>10</x></b></row>
       <row><a>7</a><b><y>20</y></b></row>
     </rows>'
  COLUMNS
    id FOR ORDINALITY,
    a INT,
    x INT PATH 'b/x',
    y INT PATH 'b/y'
)
create.select()
      .from(xmltable("//row")
        .passing(xml(
          "<rows>"
        + "<row><a>5</a><b><x>10</x></b></row>"
        + "<row><a>7</a><b><y>20</y></b></row>"
        + "</rows>"
        ))
        .column("id").forOrdinality()
        .column("a", INTEGER)
        .column("x", INTEGER).path("b/x")
        .column("y", INTEGER).path("b/y"))
      .fetch();

The result would look like this:

+----+---+----+----+
| ID | A |  X |  Y |
+----+---+----+----+
|  1 | 5 | 10 |    |
|  2 | 7 |    | 20 |
+----+---+----+----+

Dialect support

This example using jOOQ:

selectFrom(xmltable("//row").passing(xml("<rows><row><a>5</a><b><x>10</x></b></row></rows>")).column("id").forOrdinality().column("a", INTEGER).column("x", INTEGER).path("b/x"))

Translates to the following dialect specific expressions:

DB2, Teradata

SELECT id, a, x
FROM XMLTABLE(
  '//row'
  PASSING '<rows><row><a>5</a><b><x>10</x></b></row></rows>'
  COLUMNS
    id FOR ORDINALITY,
    a integer,
    x integer PATH 'b/x'
)

Hana

SELECT id, a, x
FROM XMLTABLE(
  '//row'
  PASSING '<rows><row><a>5</a><b><x>10</x></b></row></rows>'
  COLUMNS
    id FOR ORDINALITY,
    a integer PATH 'a',
    x integer PATH 'b/x'
)

Oracle

SELECT id, a, x
FROM XMLTABLE(
  '//row'
  PASSING '<rows><row><a>5</a><b><x>10</x></b></row></rows>'
  COLUMNS
    id FOR ORDINALITY,
    a number(10),
    x number(10) PATH 'b/x'
)

Postgres

SELECT id, a, x
FROM XMLTABLE(
  '//row'
  PASSING CAST('<rows><row><a>5</a><b><x>10</x></b></row></rows>' AS xml)
  COLUMNS
    id FOR ORDINALITY,
    a int,
    x int PATH 'b/x'
)

SQLServer

SELECT id, a, x
FROM (
  SELECT
    row_number() OVER (ORDER BY (
      SELECT 1
    )) id,
    t2.x.value('(a)[1]', 'int') a,
    t2.x.value('(b/x)[1]', 'int') x
  FROM (
    SELECT CAST('<rows><row><a>5</a><b><x>10</x></b></row></rows>' AS xml)
  ) t1 (x)
    CROSS APPLY t1.x.nodes('//row') t2 (x)
)

ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, CockroachDB, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Trino, Vertica, YugabyteDB

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo