New versions: Dev (3.15) | Latest (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(
          "<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 |
+----+---+----+----+

Feedback

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

The jOOQ Logo