The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement FOR clause

previous : next

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"}
]

The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement FOR clause

previous : next