This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
FOR 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.
FOR XML
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>
FOR JSON
JSON is just XML with less syntax and less features. So the FOR JSON syntax in SQL Server is almost the same as the above FOR XML syntax:
SELECT id, title FROM book ORDER BY id FOR JSON PATH
create.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.orderBy(BOOK.ID)
.forJSON().path()
.fetch();
This query produces a document like this:
[
{"id": 1, "title": "1984"},
{"id": 2, "title": "Animal Farm"},
{"id": 3, "title": "O Alquimista"},
{"id": 4, "title": "Brida"}
]
Feedback
Do you have any feedback about this page? We'd love to hear it!