Postgresql 中文操作指南
9.15. XML Functions #
本部分中描述的函数和类函数表达式适用于类型为 xml 的值。请参阅 Section 8.13 以获取有关 xml 类型的详细信息。用于转换至或从 xml 类型的类函数表达式 xmlparse 和 xmlserialize 在此文档中，而非本部分中进行说明。
The functions and function-like expressions described in this section operate on values of type xml. See Section 8.13 for information about the xml type. The function-like expressions xmlparse and xmlserialize for converting to and from type xml are documented there, not in this section.
使用这些函数时，通常都需要 PostgreSQL 使用 configure --with-libxml 构建。
Use of most of these functions requires PostgreSQL to have been built with configure --with-libxml.
9.15.1. Producing XML Content #
提供了一组函数和类似函数的表达式，用于根据 SQL 数据生成 XML 内容。因此，它们特别适用于将查询结果格式化为 XML 文档，以便在客户端应用程序中进行处理。
A set of functions and function-like expressions is available for producing XML content from SQL data. As such, they are particularly suitable for formatting query results into XML documents for processing in client applications.
9.15.1.1. xmlcomment #
xmlcomment ( text ) → xml函数 xmlcomment 可根据指定作为内容的文本创建包含 XML 注释的 XML 值。文本中不能包含 “—” 或以 “-” 结尾，否则生成的结构将不是有效的 XML 注释。如果参数为 null，则结果为 null。
The function xmlcomment creates an XML value containing an XML comment with the specified text as content. The text cannot contain “—” or end with a “-”, otherwise the resulting construct would not be a valid XML comment. If the argument is null, the result is null.
示例：
Example:
SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->9.15.1.2. xmlconcat #
xmlconcat ( xml [, ...] ) → xml函数 xmlconcat 可将一系列独立的 XML 值拼接起来，创建一个包含 XML 内容片段的单独值。空值将被忽略；仅当不存在非空参数时，结果才为 null。
The function xmlconcat concatenates a list of individual XML values to create a single value containing an XML content fragment. Null values are omitted; the result is only null if there are no nonnull arguments.
示例：
Example:
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');
xmlconcat
----------------------
<abc/><bar>foo</bar>如果存在 XML 声明，则按照如下方式进行组合。如果所有参数值都有相同的 XML 版本声明，则结果中将使用该版本，否则将不使用任何版本。如果所有参数值都有单独立声明值 “yes”，则将在结果中使用该值。 如果所有参数值都有单独立声明值并且至少有一个是 “no”，则结果中将使用该值。否则，结果将没有单独立声明。如果确定结果需要单独立声明而没有版本声明，则将使用带有版本 1.0 的版本声明，因为 XML 要求 XML 声明包含版本声明。在所有情况下，都将忽略并删除编码声明。
XML declarations, if present, are combined as follows. If all argument values have the same XML version declaration, that version is used in the result, else no version is used. If all argument values have the standalone declaration value “yes”, then that value is used in the result. If all argument values have a standalone declaration value and at least one is “no”, then that is used in the result. Else the result will have no standalone declaration. If the result is determined to require a standalone declaration but no version declaration, a version declaration with version 1.0 will be used because XML requires an XML declaration to contain a version declaration. Encoding declarations are ignored and removed in all cases.
示例：
Example:
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>9.15.1.3. xmlelement #
xmlelement ( NAME name [, XMLATTRIBUTES ( attvalue [ AS attname ] [, ...] ) ] [, content [, ...]] ) → xmlxmlelement 表达式生成一个具有给定的名称、属性和内容的 XML 元素。语法中所示的 name 和 attname 项目是简单标识符，而不是值。attvalue 和 content 项目是表达式，可以生成任何 PostgreSQL 数据类型。XMLATTRIBUTES 中的参数生成 XML 元素的属性；content 值串联起来形成其内容。
The xmlelement expression produces an XML element with the given name, attributes, and content. The name and attname items shown in the syntax are simple identifiers, not values. The attvalue and content items are expressions, which can yield any PostgreSQL data type. The argument(s) within XMLATTRIBUTES generate attributes of the XML element; the content value(s) are concatenated to form its content.
示例：
Examples:
SELECT xmlelement(name foo);
xmlelement
------------
<foo/>
SELECT xmlelement(name foo, xmlattributes('xyz' as bar));
xmlelement
------------------
<foo bar="xyz"/>
SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');
xmlelement
-------------------------------------
<foo bar="2007-01-26">content</foo>通过将攻击字符替换为序列 x_HHHH , where _HHHH 转义不正确的 XML 名称的元素和属性名称 , where HHHH 是用十六进制表示的字符的 Unicode 代码点。例如：
Element and attribute names that are not valid XML names are escaped by replacing the offending characters by the sequence x_HHHH, where _HHHH is the character’s Unicode codepoint in hexadecimal notation. For example:
SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));
xmlelement
----------------------------------
<foo_x0024_bar a_x0026_b="xyz"/>如果属性值是列引用，则无需指定显式属性名称，在这种情况下，默认情况下将使用列的名称作为属性名称。在其他情况下，属性必须具有显式名称。所以，以下示例是有效的：
An explicit attribute name need not be specified if the attribute value is a column reference, in which case the column’s name will be used as the attribute name by default. In other cases, the attribute must be given an explicit name. So this example is valid:
CREATE TABLE test (a xml, b xml);
SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;但以下无效：
But these are not:
SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;如果指定了元素内容，则将根据其数据类型进行格式化。如果内容本身为 xml 类型，则可以构建复杂的 XML 文档。例如：
Element content, if specified, will be formatted according to its data type. If the content is itself of type xml, complex XML documents can be constructed. For example:
SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
xmlelement(name abc),
xmlcomment('test'),
xmlelement(name xyz));
xmlelement
----------------------------------------------
<foo bar="xyz"><abc/><!--test--><xyz/></foo>将其他类型的文本格式化成有效的 XML 字符数据。这意味着特别地，字符 <、> 和 & 将转换为实体。二进制数据（数据类型 bytea）将以 base64 或 hex 编码表示，具体取决于配置参数 xmlbinary 的设置。预期特定数据类型的特定行为将不断发展，以便将 PostgreSQL 映射与 SQL:2006 及更高版本中指定的映射保持一致，如 Section D.3.1.3 中讨论。
Content of other types will be formatted into valid XML character data. This means in particular that the characters <, >, and & will be converted to entities. Binary data (data type bytea) will be represented in base64 or hex encoding, depending on the setting of the configuration parameter xmlbinary. The particular behavior for individual data types is expected to evolve in order to align the PostgreSQL mappings with those specified in SQL:2006 and later, as discussed in Section D.3.1.3.
9.15.1.4. xmlforest #
xmlforest ( content [ AS name ] [, ...] ) → xmlxmlforest 表达式使用给定的名称和内容生成元素的 XML 林（序列）。对于 xmlelement，每个 name 都必须是简单标识符，而 content 表达式可以具有任何数据类型。
The xmlforest expression produces an XML forest (sequence) of elements using the given names and content. As for xmlelement, each name must be a simple identifier, while the content expressions can have any data type.
示例：
Examples:
SELECT xmlforest('abc' AS foo, 123 AS bar);
xmlforest
------------------------------
<foo>abc</foo><bar>123</bar>
SELECT xmlforest(table_name, column_name)
FROM information_schema.columns
WHERE table_schema = 'pg_catalog';
xmlforest
-----------------------------------------------------------------------
<table_name>pg_authid</table_name><column_name>rolname</column_name>
<table_name>pg_authid</table_name><column_name>rolsuper</column_name>
...如第二个示例中所示，如果内容值为列引用，则可以省略元素名称，在这种情况下，默认情况下将使用列名称。否则，必须指定名称。
As seen in the second example, the element name can be omitted if the content value is a column reference, in which case the column name is used by default. Otherwise, a name must be specified.
无效的 XML 名称的元素名称的转义方式如上文 xmlelement 中所示。类似地，内容数据将被转义为有效的 XML 内容，除非它已经是 xml 类型。
Element names that are not valid XML names are escaped as shown for xmlelement above. Similarly, content data is escaped to make valid XML content, unless it is already of type xml.
请注意，如果 XML 林包含多个元素，则不是有效的 XML 文档，因此可能需要将 xmlforest 表达式包装在 xmlelement 中。
Note that XML forests are not valid XML documents if they consist of more than one element, so it might be useful to wrap xmlforest expressions in xmlelement.
9.15.1.5. xmlpi #
xmlpi ( NAME name [, content ] ) → xmlxmlpi 表达式创建一个 XML 处理指令。对于 xmlelement，name 必须是简单标识符，而 content 表达式可以具有任何数据类型。如果存在，content 必须不包含字符序列 ?>。
The xmlpi expression creates an XML processing instruction. As for xmlelement, the name must be a simple identifier, while the content expression can have any data type. The content, if present, must not contain the character sequence ?>.
示例：
Example:
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>9.15.1.6. xmlroot #
xmlroot ( xml, VERSION {text|NO VALUE} [, STANDALONE {YES|NO|NO VALUE} ] ) → xmlxmlroot 表达式更改 XML 值的根节点的属性。如果指定了版本，则它将替换根节点的版本声明中的值；如果指定了独立设置，则它将替换根节点的独立声明中的值。
The xmlroot expression alters the properties of the root node of an XML value. If a version is specified, it replaces the value in the root node’s version declaration; if a standalone setting is specified, it replaces the value in the root node’s standalone declaration.
SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),
version '1.0', standalone yes);
xmlroot
----------------------------------------
<?xml version="1.0" standalone="yes"?>
<content>abc</content>9.15.1.7. xmlagg #
xmlagg ( xml ) → xml与此处描述的其他函数不同，函数 xmlagg 是一个聚合函数。它将输入值连接到聚合函数调用，很像 xmlconcat 的工作方式，但连接是跨行发生的，而不是在单行中跨表达式发生的。请参阅 Section 9.21 以了解更多有关聚合函数的信息。
The function xmlagg is, unlike the other functions described here, an aggregate function. It concatenates the input values to the aggregate function call, much like xmlconcat does, except that concatenation occurs across rows rather than across expressions in a single row. See Section 9.21 for additional information about aggregate functions.
示例：
Example:
CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, '<foo>abc</foo>');
INSERT INTO test VALUES (2, '<bar/>');
SELECT xmlagg(x) FROM test;
xmlagg
----------------------
<foo>abc</foo><bar/>为了确定连接的顺序，可以按照 Section 4.2.7 中所述向聚合调用添加 ORDER BY 子句。例如：
To determine the order of the concatenation, an ORDER BY clause may be added to the aggregate call as described in Section 4.2.7. For example:
SELECT xmlagg(x ORDER BY y DESC) FROM test;
xmlagg
----------------------
<bar/><foo>abc</foo>以下非标准方法过去在其版本中被推荐，且在特定情况下仍可能有用：
The following non-standard approach used to be recommended in previous versions, and may still be useful in specific cases:
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>9.15.2. XML Predicates #
本部分中描述的表达式检查 xml 值的属性。
The expressions described in this section check properties of xml values.
9.15.2.1. IS DOCUMENT #
xml IS DOCUMENT → boolean如果参数 XML 值是正确的 XML 文档，则表达式 IS DOCUMENT 返回 true；如果该值不正确（即为内容片段），则返回 false；如果参数为 null，则返回 null。请参阅 Section 8.13 以了解文档和内容片段之间的区别。
The expression IS DOCUMENT returns true if the argument XML value is a proper XML document, false if it is not (that is, it is a content fragment), or null if the argument is null. See Section 8.13 about the difference between documents and content fragments.
9.15.2.2. IS NOT DOCUMENT #
xml IS NOT DOCUMENT → boolean如果参数 XML 值是一个正确的 XML 文档，则表达式 IS NOT DOCUMENT 返回 false；如果它不是（即它是内容片段），则返回 true；如果参数为 null，则返回 null。
The expression IS NOT DOCUMENT returns false if the argument XML value is a proper XML document, true if it is not (that is, it is a content fragment), or null if the argument is null.
9.15.2.3. XMLEXISTS #
XMLEXISTS ( text PASSING [BY {REF|VALUE}] xml [BY {REF|VALUE}] ) → boolean函数 xmlexists 使用已传递的 XML 值作为上下文项来评估 XPath 1.0 表达式（第一个参数）。如果该评估结果产生一个空节点集，则该函数返回 false；如果产生任何其他值，则返回 true。如果任何参数为 null，则该函数返回 null。作为上下文项传入的非 null 值必须是 XML 文档，而不是内容片段或任何非 XML 值。
The function xmlexists evaluates an XPath 1.0 expression (the first argument), with the passed XML value as its context item. The function returns false if the result of that evaluation yields an empty node-set, true if it yields any other value. The function returns null if any argument is null. A nonnull value passed as the context item must be an XML document, not a content fragment or any non-XML value.
示例：
Example:
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE '<towns><town>Toronto</town><town>Ottawa</town></towns>');
xmlexists
------------
t
(1 row)PostgreSQL 接受 BY REF 和 BY VALUE 子句，但会忽略它们，如 Section D.3.2 中所讨论的。
The BY REF and BY VALUE clauses are accepted in PostgreSQL, but are ignored, as discussed in Section D.3.2.
在 SQL 标准中，xmlexists 函数评估 XML 查询语言中的表达式，但 PostgreSQL 仅允许 XPath 1.0 表达式，如 Section D.3.1 中讨论的。
In the SQL standard, the xmlexists function evaluates an expression in the XML Query language, but PostgreSQL allows only an XPath 1.0 expression, as discussed in Section D.3.1.
9.15.2.4. xml_is_well_formed #
xml_is_well_formed ( text ) → boolean
xml_is_well_formed_document ( text ) → boolean
xml_is_well_formed_content ( text ) → boolean这些函数检查 text 字符串是否代表格式良好的 XML，返回布尔值结果。xml_is_well_formed_document 检查格式良好的文档，而 xml_is_well_formed_content 检查格式良好的内容。xml_is_well_formed 在将 xmloption 配置参数设置为 DOCUMENT 时执行前者，在将此参数设置为 CONTENT 时执行后者。这意味着 xml_is_well_formed 对于查看向类型 xml 的简单强制转换是否会成功很有用，而其他两个函数对于查看 XMLPARSE 的相应变体是否会成功很有用。
These functions check whether a text string represents well-formed XML, returning a Boolean result. xml_is_well_formed_document checks for a well-formed document, while xml_is_well_formed_content checks for well-formed content. xml_is_well_formed does the former if the xmloption configuration parameter is set to DOCUMENT, or the latter if it is set to CONTENT. This means that xml_is_well_formed is useful for seeing whether a simple cast to type xml will succeed, whereas the other two functions are useful for seeing whether the corresponding variants of XMLPARSE will succeed.
示例：
Examples:
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
xml_is_well_formed
--------------------
f
(1 row)
SELECT xml_is_well_formed('<abc/>');
xml_is_well_formed
--------------------
t
(1 row)
SET xmloption TO CONTENT;
SELECT xml_is_well_formed('abc');
xml_is_well_formed
--------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
xml_is_well_formed_document
-----------------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
xml_is_well_formed_document
-----------------------------
f
(1 row)最后一个示例表明，检查包括命名空间是否匹配正确。
The last example shows that the checks include whether namespaces are correctly matched.
9.15.3. Processing XML #
为了处理 xml 数据类型的值，PostgreSQL 提供评估 XPath 1.0 表达式的函数 xpath 和 xpath_exists 以及 XMLTABLE 表函数。
To process values of data type xml, PostgreSQL offers the functions xpath and xpath_exists, which evaluate XPath 1.0 expressions, and the XMLTABLE table function.
9.15.3.1. xpath #
xpath ( xpath text, xml xml [, nsarray text[] ] ) → xml[]函数 xpath 根据 XML 值 xml 评估 XPath 1.0 表达式 xpath（作为文本给定）。它返回与 XPath 表达式生成的节点集对应的 XML 值数组。如果 XPath 表达式返回标量值而不是节点集，则返回单元素数组。
The function xpath evaluates the XPath 1.0 expression xpath (given as text) against the XML value xml. It returns an array of XML values corresponding to the node-set produced by the XPath expression. If the XPath expression returns a scalar value rather than a node-set, a single-element array is returned.
第二个参数必须是格式良好的 XML 文档。尤其是，它必须具有一个根节点元素。
The second argument must be a well formed XML document. In particular, it must have a single root node element.
该函数的可选第三个参数是命名空间映射的数组。此数组应为一个二维 text 数组，其中第二个轴的长度等于 2（即，它应为一个数组的数组，每个数组都恰好包含 2 个元素）。每个数组条目的第一个元素是命名空间名称（别名），第二个元素是命名空间 URI。不必要求此数组中提供的别名与 XML 文档本身所使用的别名相同（换句话说，在 XML 文档和 xpath 函数上下文中，别名是 local）。
The optional third argument of the function is an array of namespace mappings. This array should be a two-dimensional text array with the length of the second axis being equal to 2 (i.e., it should be an array of arrays, each of which consists of exactly 2 elements). The first element of each array entry is the namespace name (alias), the second the namespace URI. It is not required that aliases provided in this array be the same as those being used in the XML document itself (in other words, both in the XML document and in the xpath function context, aliases are local).
示例：
Example:
SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath
--------
{test}
(1 row)若要处理默认（匿名）命名空间，请执行以下操作：
To deal with default (anonymous) namespaces, do something like this:
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
ARRAY[ARRAY['mydefns', 'http://example.com']]);
xpath
--------
{test}
(1 row)9.15.3.2. xpath_exists #
xpath_exists ( xpath text, xml xml [, nsarray text[] ] ) → boolean函数 xpath_exists 是 xpath 函数的专门形式。此函数不返回满足 XPath 1.0 表达式的各个 XML 值，而是返回一个布尔值，指示查询是否已满足（具体来说，是否产生了除空节点集外的任何其他值）。此函数等效于 XMLEXISTS 谓词，不同之处在于它还支持命名空间映射参数。
The function xpath_exists is a specialized form of the xpath function. Instead of returning the individual XML values that satisfy the XPath 1.0 expression, this function returns a Boolean indicating whether the query was satisfied or not (specifically, whether it produced any value other than an empty node-set). This function is equivalent to the XMLEXISTS predicate, except that it also offers support for a namespace mapping argument.
示例：
Example:
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath_exists
--------------
t
(1 row)9.15.3.3. xmltable #
XMLTABLE (
[ XMLNAMESPACES ( namespace_uri AS namespace_name [, ...] ), ]
row_expression PASSING [BY {REF|VALUE}] document_expression [BY {REF|VALUE}]
COLUMNS name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL]
| FOR ORDINALITY }
[, ...]
) → setof recordxmltable 表达式根据 XML 值、XPath 过滤器（用于提取行）和一组列定义生成一个表。尽管它在语法上类似于一个函数，但它只能显示为查询的 FROM 子句中的一个表。
The xmltable expression produces a table based on an XML value, an XPath filter to extract rows, and a set of column definitions. Although it syntactically resembles a function, it can only appear as a table in a query’s FROM clause.
可选的 XMLNAMESPACES 子句给出命名空间定义的逗号分隔列表，其中每个 namespace_uri 是一个 text 表达式，每个 namespace_name 是一个简单标识符。其指定文档中使用的 XML 命名空间及它们的别名。当前不支持默认命名空间规范。
The optional XMLNAMESPACES clause gives a comma-separated list of namespace definitions, where each namespace_uri is a text expression and each namespace_name is a simple identifier. It specifies the XML namespaces used in the document and their aliases. A default namespace specification is not currently supported.
必需的 row_expression 参数是 XPath 1.0 表达式（作为 text 给定），经过评估（以 XML 值 document_expression 作为其上下文项）后，可获取一个 XML 节点集。xmltable 将这些节点转换为输出行。如果 document_expression 是 null，或者 row_expression 生成了空节点集或任何非节点集的值，则不会生成任何行。
The required row_expression argument is an XPath 1.0 expression (given as text) that is evaluated, passing the XML value document_expression as its context item, to obtain a set of XML nodes. These nodes are what xmltable transforms into output rows. No rows will be produced if the document_expression is null, nor if the row_expression produces an empty node-set or any value other than a node-set.
_document_expression_为 _row_expression_提供上下文项。它必须是一个格式良好的 XML 文档；不接受片段/树林。正如 Section D.3.2中讨论的那样，_BY REF_和 _BY VALUE_子句可被接受，但会被忽略。
document_expression provides the context item for the row_expression. It must be a well-formed XML document; fragments/forests are not accepted. The BY REF and BY VALUE clauses are accepted but ignored, as discussed in Section D.3.2.
在 SQL 标准中，_xmltable_函数评估 XML 查询语言中的表达式，但 PostgreSQL 仅允许 XPath 1.0 表达式，正如 Section D.3.1中讨论的那样。
In the SQL standard, the xmltable function evaluates expressions in the XML Query language, but PostgreSQL allows only XPath 1.0 expressions, as discussed in Section D.3.1.
必需的 COLUMNS 条款指定将在输出表中生成的列。请参见上方的语法摘要了解更多格式。每个列都需要一个名称，就像一个数据类型一样（除非指定了 FOR ORDINALITY，在这种情况下，则类型 integer 为隐式）。路径、默认值和可空性条款是可选的。
The required COLUMNS clause specifies the column(s) that will be produced in the output table. See the syntax summary above for the format. A name is required for each column, as is a data type (unless FOR ORDINALITY is specified, in which case type integer is implicit). The path, default and nullability clauses are optional.
标记为 FOR ORDINALITY 的列将使用行数填充，从 row_expression 结果节点集中检索到的节点的顺序从 1 开始。最多只能标记一个列为 FOR ORDINALITY。
A column marked FOR ORDINALITY will be populated with row numbers, starting with 1, in the order of nodes retrieved from the row_expression's result node-set. At most one column may be marked FOR ORDINALITY.
Note
XPath 1.0 未为节点集中节点指定顺序，因此依赖结果特定顺序的代码将取决于实现。可在 Section D.3.1.2中找到详细信息。
XPath 1.0 does not specify an order for nodes in a node-set, so code that relies on a particular order of the results will be implementation-dependent. Details can be found in Section D.3.1.2.
列的 column_expression 是 XPath 1.0 表达式，它会对使用 row_expression 结果作为其上下文项目的 row_expression 结果的每一行进行评估，以查找该列的值。如果没有给出 column_expression，则列名称将被用作隐式路径。
The column_expression for a column is an XPath 1.0 expression that is evaluated for each row, with the current node from the row_expression result as its context item, to find the value of the column. If no column_expression is given, then the column name is used as an implicit path.
如果某个列的 XPath 表达式返回了一个非 XML 值（在 XPath 1.0 中被限制为字符串、布尔或双精度），并且该列具有 xml 之外的 PostgreSQL 类型，则该列将被设置，就像通过将该值的字符串表示形式分配给 PostgreSQL 类型一样。（如果该值是布尔，则其字符串表示形式会被视为 1 或 0（如果输出列的类型类别是数字），否则会被视为 true 或 false。）
If a column’s XPath expression returns a non-XML value (which is limited to string, boolean, or double in XPath 1.0) and the column has a PostgreSQL type other than xml, the column will be set as if by assigning the value’s string representation to the PostgreSQL type. (If the value is a boolean, its string representation is taken to be 1 or 0 if the output column’s type category is numeric, otherwise true or false.)
如果列的 XPath 表达式返回一个非空 XML 节点集合，并且该列 PostgreSQL 类型为 xml ，当该集合是文档或内容形式时，该列将被准确分配表达式结果。 [8 ]
If a column’s XPath expression returns a non-empty set of XML nodes and the column’s PostgreSQL type is xml, the column will be assigned the expression result exactly, if it is of document or content form. [8]
一个 non-XML 结果分配给了 xml 输出列生成了内容，一个包含该结果字符串值的单独文本节点。分配给任何其他类型的列的 XML 结果不能有多于一个节点，否则将引发错误。如果确切地只有一个节点，则将设置该列，就像通过将该节点的字符串值（如为 XPath 1.0 string 函数所定义）分配给 PostgreSQL 类型一样。
A non-XML result assigned to an xml output column produces content, a single text node with the string value of the result. An XML result assigned to a column of any other type may not have more than one node, or an error is raised. If there is exactly one node, the column will be set as if by assigning the node’s string value (as defined for the XPath 1.0 string function) to the PostgreSQL type.
XML 元素的字符串值是对该元素中包含的所有文本节点及其后代的连接，按照文档顺序连接。没有后代文本节点的元素的字符串值是一个空字符串（不是 NULL）。任何 xsi:nil 属性都会被忽略。请注意，两个非文本元素之间的仅空格 text() 节点会被保留，并且 text() 节点的预留空格不会被展平。可以对 XPath 1.0 string 函数进行咨询，了解定义其他 XML 节点类型和 non-XML 值字符串的规则。
The string value of an XML element is the concatenation, in document order, of all text nodes contained in that element and its descendants. The string value of an element with no descendant text nodes is an empty string (not NULL). Any xsi:nil attributes are ignored. Note that the whitespace-only text() node between two non-text elements is preserved, and that leading whitespace on a text() node is not flattened. The XPath 1.0 string function may be consulted for the rules defining the string value of other XML node types and non-XML values.
此处提出的转换规则与 SQL 标准的规则不完全相同，正如 Section D.3.1.3中讨论的那样。
The conversion rules presented here are not exactly those of the SQL standard, as discussed in Section D.3.1.3.
如果对于给定的行，路径表达式返回了一个空节点集（通常在它不匹配时），则该列将被设置为 NULL，除非指定了 default_expression；之后，使用评估该表达式的结果值。
If the path expression returns an empty node-set (typically, when it does not match) for a given row, the column will be set to NULL, unless a default_expression is specified; then the value resulting from evaluating that expression is used.
default_expression 没有在调用 xmltable 时立即进行评估，而是会在每次列需要默认值时进行评估。如果表达式符合稳定或不可变的条件，则可以跳过重复评估。这意味着你可以在 default_expression 中合理地使用诸如 nextval 的易失函数。
A default_expression, rather than being evaluated immediately when xmltable is called, is evaluated each time a default is needed for the column. If the expression qualifies as stable or immutable, the repeat evaluation may be skipped. This means that you can usefully use volatile functions like nextval in default_expression.
可以将列标记为 NOT NULL。如果 NOT NULL 列的 column_expression 与任何内容不匹配，并且没有 DEFAULT，或者 default_expression 也评估为 null，则会报告错误。
Columns may be marked NOT NULL. If the column_expression for a NOT NULL column does not match anything and there is no DEFAULT or the default_expression also evaluates to null, an error is reported.
示例：
Examples:
CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
<ROW id="1">
<COUNTRY_ID>AU</COUNTRY_ID>
<COUNTRY_NAME>Australia</COUNTRY_NAME>
</ROW>
<ROW id="5">
<COUNTRY_ID>JP</COUNTRY_ID>
<COUNTRY_NAME>Japan</COUNTRY_NAME>
<PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
<SIZE unit="sq_mi">145935</SIZE>
</ROW>
<ROW id="6">
<COUNTRY_ID>SG</COUNTRY_ID>
<COUNTRY_NAME>Singapore</COUNTRY_NAME>
<SIZE unit="sq_km">697</SIZE>
</ROW>
</ROWS>
$$ AS data;
SELECT xmltable.*
FROM xmldata,
XMLTABLE('//ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
ordinality FOR ORDINALITY,
"COUNTRY_NAME" text,
country_id text PATH 'COUNTRY_ID',
size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
size_other text PATH
'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
id | ordinality | COUNTRY_NAME | country_id | size_sq_km | size_other | premier_name
----+------------+--------------+------------+------------+--------------+---------------
1 | 1 | Australia | AU | | | not specified
5 | 2 | Japan | JP | | 145935 sq_mi | Shinzo Abe
6 | 3 | Singapore | SG | 697 | | not specified以下示例显示了多个 text() 节点的连接、将列名称用作 XPath 过滤器以及对空格、XML 注释和处理指令的处理：
The following example shows concatenation of multiple text() nodes, usage of the column name as XPath filter, and the treatment of whitespace, XML comments and processing instructions:
CREATE TABLE xmlelements AS SELECT
xml $$
<root>
<element> Hello<!-- xyxxz -->2a2<?aaaaa?> <!--x--> bbb<x>xxx</x>CC </element>
</root>
$$ AS data;
SELECT xmltable.*
FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);
element
-------------------------
Hello2a2 bbbxxxCC以下示例说明了如何使用 XMLNAMESPACES 条款来指定 XML 文档和 XPath 表达式中使用的名称空间的列表：
The following example illustrates how the XMLNAMESPACES clause can be used to specify a list of namespaces used in the XML document as well as in the XPath expressions:
WITH xmldata(data) AS (VALUES ('
<example xmlns="http://example.com/myns" xmlns:B="http://example.com/b">
<item foo="1" B:bar="2"/>
<item foo="3" B:bar="4"/>
<item foo="4" B:bar="5"/>
</example>'::xml)
)
SELECT xmltable.*
FROM XMLTABLE(XMLNAMESPACES('http://example.com/myns' AS x,
'http://example.com/b' AS "B"),
'/x:example/x:item'
PASSING (SELECT data FROM xmldata)
COLUMNS foo int PATH '@foo',
bar int PATH '@B:bar');
foo | bar
-----+-----
1 | 2
3 | 4
4 | 5
(3 rows)9.15.4. Mapping Tables to XML #
以下函数将关系表的内容映射到 XML 值。可以将它们视为 XML 导出功能：
The following functions map the contents of relational tables to XML values. They can be thought of as XML export functionality:
table_to_xml ( table regclass, nulls boolean,
tableforest boolean, targetns text ) → xml
query_to_xml ( query text, nulls boolean,
tableforest boolean, targetns text ) → xml
cursor_to_xml ( cursor refcursor, count integer, nulls boolean,
tableforest boolean, targetns text ) → xml_table_to_xml_映射作为参数 _table_传递的已命名表的正文。_regclass_类型的接受字符串，用常规表示法（包括可选项的架构限定符和双引号）标识表，有关详情，请参见 Section 8.19。_query_to_xml_执行文本作为参数 _query_传递的查询，并映射结果集。_cursor_to_xml_从参数 _cursor_指定的游标中获取指定数量的行。如果必须映射大型表，则建议使用此变量，因为结果值是由每个函数在内存中构建的。
table_to_xml maps the content of the named table, passed as parameter table. The regclass type accepts strings identifying tables using the usual notation, including optional schema qualification and double quotes (see Section 8.19 for details). query_to_xml executes the query whose text is passed as parameter query and maps the result set. cursor_to_xml fetches the indicated number of rows from the cursor specified by the parameter cursor. This variant is recommended if large tables have to be mapped, because the result value is built up in memory by each function.
如果 tableforest 为假，则生成的 XML 文档看起来像这样：
If tableforest is false, then the resulting XML document looks like this:
<tablename>
<row>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</row>
<row>
...
</row>
...
</tablename>如果 tableforest 为真，则结果是一个 XML 内容片段，看起来像这样：
If tableforest is true, the result is an XML content fragment that looks like this:
<tablename>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</tablename>
<tablename>
...
</tablename>
...如果表不可用，也就是说，在映射查询或游标时，在第一种格式中使用了字符串 table，在第二种格式中使用了 row。
If no table name is available, that is, when mapping a query or a cursor, the string table is used in the first format, row in the second format.
由用户选择此格式。第一种格式是适当的 XML 文档，这在许多应用程序中非常重要。如果结果值稍后要重新装配到一个文档中，第二种格式通常更适合 cursor_to_xml 函数。特定 xmlelement 的上述生成 XML 内容函数可用于按需更改结果。
The choice between these formats is up to the user. The first format is a proper XML document, which will be important in many applications. The second format tends to be more useful in the cursor_to_xml function if the result values are to be reassembled into one document later on. The functions for producing XML content discussed above, in particular xmlelement, can be used to alter the results to taste.
以同一种方式映射数据值，如上文 xmlelement 函数中所述。
The data values are mapped in the same way as described for the function xmlelement above.
参数 nulls 确定 null 值是否应包含在输出中。如果为 true，则 null 列值表示为：
The parameter nulls determines whether null values should be included in the output. If true, null values in columns are represented as:
<columnname xsi:nil="true"/>其中 xsi 是 XML Schema Instance 的 XML 命名空间前缀。相应的名称空间声明将被添加到结果值中。如果为 false，则只从输出中省略包含 null 值的列。
where xsi is the XML namespace prefix for XML Schema Instance. An appropriate namespace declaration will be added to the result value. If false, columns containing null values are simply omitted from the output.
参数 targetns 指定结果的所需 XML 命名空间。如果没有想要的特定命名空间，则应传递一个空字符串。
The parameter targetns specifies the desired XML namespace of the result. If no particular namespace is wanted, an empty string should be passed.
以下函数返回描述上述相应函数执行的映射的 XML Schema 文档：
The following functions return XML Schema documents describing the mappings performed by the corresponding functions above:
table_to_xmlschema ( table regclass, nulls boolean,
tableforest boolean, targetns text ) → xml
query_to_xmlschema ( query text, nulls boolean,
tableforest boolean, targetns text ) → xml
cursor_to_xmlschema ( cursor refcursor, nulls boolean,
tableforest boolean, targetns text ) → xml必须传递的参数相同，用于获取匹配 XML 数据映射和 XML Schema 文档。
It is essential that the same parameters are passed in order to obtain matching XML data mappings and XML Schema documents.
以下函数在一个文档（或林）中生成 XML 数据映射和相应的 XML Schema，并将它们链接在一起。它们在想要自包含的、自我描述的结果时非常有用：
The following functions produce XML data mappings and the corresponding XML Schema in one document (or forest), linked together. They can be useful where self-contained and self-describing results are wanted:
table_to_xml_and_xmlschema ( table regclass, nulls boolean,
tableforest boolean, targetns text ) → xml
query_to_xml_and_xmlschema ( query text, nulls boolean,
tableforest boolean, targetns text ) → xml此外，还可以使用以下函数生成整个 schema 或整个当前数据库的类似映射：
In addition, the following functions are available to produce analogous mappings of entire schemas or the entire current database:
schema_to_xml ( schema name, nulls boolean,
tableforest boolean, targetns text ) → xml
schema_to_xmlschema ( schema name, nulls boolean,
tableforest boolean, targetns text ) → xml
schema_to_xml_and_xmlschema ( schema name, nulls boolean,
tableforest boolean, targetns text ) → xml
database_to_xml ( nulls boolean,
tableforest boolean, targetns text ) → xml
database_to_xmlschema ( nulls boolean,
tableforest boolean, targetns text ) → xml
database_to_xml_and_xmlschema ( nulls boolean,
tableforest boolean, targetns text ) → xml这些函数忽略当前用户不可读的表。此外，数据库级函数还忽略当前用户没有 USAGE（查找）权限的 schema。
These functions ignore tables that are not readable by the current user. The database-wide functions additionally ignore schemas that the current user does not have USAGE (lookup) privilege for.
请注意，这些函数可能产生大量需要在内存中建立的数据。当请求大型 schema 或数据库的内容映射时，不妨考虑分别映射表，甚至可能通过游标映射表。
Note that these potentially produce a lot of data, which needs to be built up in memory. When requesting content mappings of large schemas or databases, it might be worthwhile to consider mapping the tables separately instead, possibly even through a cursor.
schema 内容映射的结果如下所示：
The result of a schema content mapping looks like this:
<schemaname>
table1-mapping
table2-mapping
...
</schemaname>其中表映射的格式取决于如上文所述 tableforest 参数。
where the format of a table mapping depends on the tableforest parameter as explained above.
数据库内容映射的结果如下所示：
The result of a database content mapping looks like this:
<dbname>
<schema1name>
...
</schema1name>
<schema2name>
...
</schema2name>
...
</dbname>其中 schema 映射与上述相同。
where the schema mapping is as above.
作为使用这些函数所生成输出的一个示例， Example 9.1显示一个 XSLT 样式表，该样式表将 _table_to_xml_and_xmlschema_的输出转换为一个 HTML 文档，其中包含表数据的表格形式。以类似的方式，可以将这些函数的结果转换为其他基于 XML 的格式。
As an example of using the output produced by these functions, Example 9.1 shows an XSLT stylesheet that converts the output of table_to_xml_and_xmlschema to an HTML document containing a tabular rendition of the table data. In a similar manner, the results from these functions can be converted into other XML-based formats.
Example 9.1. XSLT Stylesheet for Converting SQL/XML Output to HTML
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.w3.org/1999/xhtml"
>
<xsl:output method="xml"
doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN"
indent="yes"/>
<xsl:template match="/*">
<xsl:variable name="schema" select="//xsd:schema"/>
<xsl:variable name="tabletypename"
select="$schema/xsd:element[@name=name(current())]/@type"/>
<xsl:variable name="rowtypename"
select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/>
<html>
<head>
<title><xsl:value-of select="name(current())"/></title>
</head>
<body>
<table>
<tr>
<xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name">
<th><xsl:value-of select="."/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="row">
<tr>
<xsl:for-each select="*">
<td><xsl:value-of select="."/></td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>[8 ] 一个在顶层包含多个元素节点或在元素外部包含非空白文本的结果是一个内容形式示例。一个 XPath 结果可以不是任何形式，例如如果它返回一个从包含它的元素中选择的属性节点。这样的结果将被放入内容形式，其中用为 XPath 1.0 string 函数定义的字符串值替换每个这种不允许的节点。
[8] A result containing more than one element node at the top level, or non-whitespace text outside of an element, is an example of content form. An XPath result can be of neither form, for example if it returns an attribute node selected from the element that contains it. Such a result will be put into content form with each such disallowed node replaced by its string value, as defined for the XPath 1.0 string function.