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

previous : next

AUTO mode

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

The AUTO mode generates XML content based on automatically generated elements that model the query structure. This mode doesn't provide much control over the output.

Consider the following query

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

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

This query produces a document fragment like this:

<test.dbo.book ID="1" TITLE="1984"/>
<test.dbo.book ID="2" TITLE="Animal Farm"/>
<test.dbo.book ID="3" TITLE="O Alquimista"/>
<test.dbo.book ID="4" TITLE="Brida"/>

Dialect support

This example using jOOQ:

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

Translates to the following dialect specific expressions:

DB2, Oracle, Postgres

SELECT xmlagg(xmlelement(
  NAME BOOK,
  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 AUTO
)

Sybase

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

Teradata

SELECT xmlagg(xmlelement(
  NAME BOOK,
  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 AUTO mode

previous : next