PATH模式
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
PATH 模式基于列别名指定的“路径”生成 XML 内容。
考虑以下查询
SELECT id AS [book/id], title AS [book/title] FROM book ORDER BY id FOR XML PATH
create.select(
BOOK.ID.as("book/id"),
BOOK.TITLE.as("book/title"))
.from(BOOK)
.orderBy(BOOK.ID)
.forXML().path()
.fetch();
此查询会生成如下文档片段
<row><book><ID>1</ID><TITLE>1984</TITLE></book></row> <row><book><ID>2</ID><TITLE>Animal Farm</TITLE></book></row> <row><book><ID>3</ID><TITLE>O Alquimista</TITLE></book></row> <row><book><ID>4</ID><TITLE>Brida</TITLE></book></row>
或者，为行提供显式元素名称（默认值为 row，如上所示）
SELECT id, title
FROM book
ORDER BY id
FOR XML PATH ('book')
create.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.orderBy(BOOK.ID)
.forXML().path("book")
.fetch();
这将产生
<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>
方言支持
此示例使用 jOOQ
select(BOOK.ID.as(quotedName("book/id"))).from(BOOK).orderBy(BOOK.ID).forXML().path()
翻译成以下特定方言的表达式
DB2, Oracle, Postgres
SELECT xmlagg(xmlelement(
NAME row,
xmlelement(
NAME book,
xmlelement(NAME id, "book/id")
)
))
FROM (
SELECT BOOK.ID "book/id"
FROM BOOK
ORDER BY BOOK.ID
) t
SQLServer
SELECT ( SELECT BOOK.ID [book/id] FROM BOOK ORDER BY BOOK.ID FOR XML PATH )
Teradata
SELECT xmlagg(xmlelement(
NAME row,
xmlelement(
NAME book,
xmlelement(NAME id, "book/id")
)
))
FROM (
SELECT *
FROM (
SELECT TOP 999999999999999999 BOOK.ID "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, Sybase, Trino, Vertica, YugabyteDB
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！