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!