FOR XML clause
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
While both XML and JSON usage in SQL has been standardised in more recent versions of the SQL standard, SQL Server has always had some very convenient utilities at the end of a SELECT statement, which allow for converting SQL tables into the most common XML or JSON representations.
Starting with jOOQ 3.14, these syntaxes are supported in jOOQ as well, and if possible, emulated in other dialects which have native XML or JSON support.
Consider the following query
SELECT id, title
FROM book
ORDER BY id
FOR XML PATH ('book'), ROOT ('books')
create.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.orderBy(BOOK.ID)
.forXML().path("book").root("books")
.fetch();
This query produces a document like this:
<books> <book><id>1</id><title>1984</title></book> <book><id>2</id><title>Animal Farm</title></book> <book><id>3</id><title>O Alquimista</title></book> <book><id>4</id><title>Brida</title></book> </books>
Table of contents
- 3.5.3.13.1.
- AUTO mode
- 3.5.3.13.2.
- PATH mode
- 3.5.3.13.3.
- RAW mode
- 3.5.3.13.4.
- ROOT directive
- 3.5.3.13.5.
- ELEMENTS directive
| previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!