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, ClickHouse, CockroachDB, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Trino, Vertica, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!