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

previous : next

RAW mode

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The RAW mode generates XML content based on the structure of the result set without any nesting capabilities. This mode doesn't provide much control over the output.

Consider the following query

SELECT id, title
FROM book
ORDER BY id
FOR XML RAW
 

create.select(BOOK.ID, BOOK.TITLE)
      .from(BOOK)
      .orderBy(BOOK.ID)
      .forXML().raw()
      .fetch();

This query produces a document fragment like this:

<row ID="1" TITLE="1984"/>
<row ID="2" TITLE="Animal Farm"/>
<row ID="3" TITLE="O Alquimista"/>
<row ID="4" TITLE="Brida"/>

Dialect support

This example using jOOQ:

select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).forXML().raw()

Translates to the following dialect specific expressions:

DB2, Oracle, Postgres

SELECT xmlagg(xmlelement(
  NAME row,
  xmlattributes(t.ID AS ID)
))
FROM (
  SELECT BOOK.ID
  FROM BOOK
  ORDER BY BOOK.ID
) t

SQLServer

SELECT (
  SELECT BOOK.ID
  FROM BOOK
  ORDER BY BOOK.ID
  FOR XML RAW
)

Sybase

SELECT (
  SELECT BOOK.ID
  FROM BOOK
  ORDER BY BOOK.ID
  FOR XML RAW
)
FROM SYS.DUMMY

Teradata

SELECT xmlagg(xmlelement(
  NAME row,
  xmlattributes(t.ID AS ID)
))
FROM (
  SELECT *
  FROM (
    SELECT TOP 999999999999999999 BOOK.ID
    FROM BOOK
    ORDER BY BOOK.ID
  ) x
) t

ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Trino, Vertica, YugabyteDB

/* UNSUPPORTED */

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

previous : next