## 9.15.XML函数

[9.15.1. 生成XML内容](functions-xml.html#FUNCTIONS-PRODUCING-XML)

[9.15.2. XML谓词](functions-xml.html#FUNCTIONS-XML-PREDICATES)

[9.15.3. 处理XML](functions-xml.html#FUNCTIONS-XML-PROCESSING)

[9.15.4. 将表映射到XML](functions-xml.html#FUNCTIONS-XML-MAPPING)

[](<>)

本节中描述的函数和类似函数的表达式对类型为`xml`看见[第8.13节](datatype-xml.html)有关`xml`类型函数式表达式`xmlparse`和`xmlserialize`用于转换类型和从类型转换`xml`记录在那里，而不是本节中。

使用这些函数中的大多数都需要使用PostgreSQL构建`配置--使用libxml`.

### 9.15.1.生成XML内容

一组函数和类似函数的表达式可用于从SQL数据生成XML内容。因此，它们特别适合将查询结果格式化为XML文档，以便在客户端应用程序中进行处理。

#### 9.15.1.1.`xmlcomment`

[](<>)

```
xmlcomment ( text ) → xml
```

功能`xmlcomment`创建一个包含XML注释的XML值，该注释的内容为指定的文本。文本不能包含“`--`“或者以`-`，否则生成的构造将不是有效的XML注释。如果参数为null，则结果为null。

例子：

```
SELECT xmlcomment('hello');

  xmlcomment
#### 9.15.1.2. `xmlconcat`

[]()
```

xmlcontat（xml）[, ...] ) → xml

```
 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:
```

选择xmlconcat（'<abc/>', '<bar>福</bar>');

```
  xmlconcat
```

#### 9.15.1.3. `xmlelement`

[](<>)

```
xmlelement ( NAME name [, XMLATTRIBUTES ( attvalue [ AS attname ] [, ...] ) ] [, content [, ...]] ) → xml
```

这个`xmlelement`表达式生成具有给定名称、属性和内容的XML元素。这个*`名称`*和*`阿特名`*语法中显示的项是简单的标识符，而不是值。这个*`attvalue`*和*`所容纳之物`*项是表达式，可以生成任何PostgreSQL数据类型。其中的论点`XMLATTRIBUTES`生成XML元素的属性；这个*`所容纳之物`*值被连接以形成其内容。

例如：

```
SELECT xmlelement(name foo);

 xmlelement
#### 9.15.1.4. `xmlforest`

[]()
```

xmlforest（内容）[作为名字][, ...] ) → xml

```
 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:
```

选择xmlforest（“abc”作为foo，123作为bar）；

```
      xmlforest
```

#### 9.15.1.5. `xmlpi`

[](<>)

```
xmlpi ( NAME name [, content ] ) → xml
```

这个`xmlpi`表达式创建XML处理指令。至于`xmlelement`这个*`名称`*必须是简单标识符，而*`所容纳之物`*表达式可以有任何数据类型。这个*`所容纳之物`*，如果存在，则不能包含字符序列`?>`.

例子：

```
SELECT xmlpi(name php, 'echo "hello world";');

            xmlpi
#### 9.15.1.6. `xmlroot`

[]()
```

xmlroot（xml，版本{text | NO VALUE}[，独立,YES { NO | NO VALUE|] ) → 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.
```

选择xmlroot（xmlparse（文档）<?xml version="1.1"?><content>abc</content>，版本“1.0”，是）；

```
            xmlroot
```

#### 9.15.1.7. `xmlagg`

[](<>)

```
xmlagg ( xml ) → xml
```

功能`xmlagg`与这里描述的其他函数不同，它是一个聚合函数。它将输入值连接到聚合函数调用，就像`xmlconcat`除了连接发生在行之间，而不是发生在单行中的表达式之间。看见[第9.21节](functions-aggregate.html)有关聚合函数的更多信息。

例子：

```
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
### 9.15.2. XML Predicates

 The expressions described in this section check properties of `xml` values.

#### 9.15.2.1. `IS DOCUMENT`

[]()
```

xml是一种文档→ 布尔值

```
 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](datatype-xml.html) about the difference between documents and content fragments.

#### 9.15.2.2. `IS NOT DOCUMENT`

[]()
```

xml不是文档→ 布尔值

```
 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（文本传递）[通过{REF | VALUE}] xml [通过{REF | VALUE}]) → 布尔值

```
 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:
```

选择xmlexists（'//town）[text（）()“多伦多”=]“按价值传递”<towns><town>多伦多</town><town>渥太华</town></towns>');

 xmlexists

#### 9.15.2.4. `xml格式良好`

[](<>)[](<>)[](<>)

```
xml_is_well_formed ( text ) → boolean
xml_is_well_formed_document ( text ) → boolean
xml_is_well_formed_content ( text ) → boolean
```

这些函数检查`文本`字符串表示格式良好的XML，返回布尔结果。`xml是格式良好的文档`检查格式良好的文档，而`xml是格式良好的内容`检查格式良好的内容。`xml格式良好`前者是不是[xmloption](runtime-config-client.html#GUC-XMLOPTION)配置参数设置为`文件`，如果设置为`所容纳之物`.这意味着`xml格式良好`用于查看简单的强制转换是否需要键入`xml`将成功，而其他两个函数对于查看`XMLPARSE`他会成功的。

例如：

```
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
 xml_is_well_formed
### 9.15.3. Processing XML

 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文本、xml\[、nsarray文本\[]）→ xml\[]

```
 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.

 The second argument must be a well formed XML document. In particular, it must have a single root node element.

 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:
```

选择xpath（“/my:a/text（）”，“\<my:a xmlns:my=”[http://example.com“>测试](http://example.com">test)\</my:a>，数组\[ARRAY][“我的”http'example',com']]);

 xpath  

#### 9.15.3.2. `xpath_存在`

[](<>)

```
xpath_exists ( xpath text, xml xml [, nsarray text[] ] ) → boolean
```

功能`xpath_存在`是一种特殊形式的`xpath`作用该函数不返回满足XPath 1.0表达式的单个XML值，而是返回一个布尔值，指示查询是否满足（具体来说，它是否生成除空节点集以外的任何值）。此函数相当于`XMLEXISTS`谓词，但它还提供对命名空间映射参数的支持。

例子：

```
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
                     ARRAY[ARRAY['my', 'http://example.com']]);

 xpath_exists  
#### 9.15.3.3. `xmltable`

[]()[]()
```

XMLTABLE（\[XMLNAMESPACES（namespace_uri作为namespace_name[, ...])，]行\_表达式传递[通过{REF | VALUE}]文档\_表达式[通过{REF | VALUE}]列名称{type[路径列\_表达式][default default_expression] [非空|空]|对于一般性}[, ...]) → 一套记录

```
 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.

 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.

 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`* 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](xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-POSTGRESQL).

 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](xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-XPATH1).

 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.

 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 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](xml-limits-conformance.html#XML-XPATH-1-SPECIFICS).

 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.

 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`.)

 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. [<sup class="footnote" id="id-1.5.8.21.7.5.15.2">[8]</sup>](#ftn.id-1.5.8.21.7.5.15.2)

 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.

 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.

 The conversion rules presented here are not exactly those of the SQL standard, as discussed in [Section D.3.1.3](xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-CASTS).

 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.

 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`*.

 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:
```

创建表xmldata作为SELECT xml$$<ROWS>
  <ROW id="1">\<COUNTRY\\u ID>AU\</COUNTRY\\u ID>\<COUNTRY\\u NAME>澳大利亚\</COUNTRY\\u NAME></ROW>
  <ROW id="5">\<COUNTRY\\u ID>JP\</COUNTRY\\u ID>\<COUNTRY\\u NAME>日本\</COUNTRY\\u NAME>\<PREMIER\\u NAME>安倍晋三\</PREMIER\\u NAME><SIZE unit="sq_mi">145935</SIZE>
  </ROW>
  <ROW id="6">\<COUNTRY\\u ID>SG\</COUNTRY\\u ID>\<COUNTRY\\u NAME>新加坡\</COUNTRY\\u NAME><SIZE unit="sq_km">697</SIZE>
  </ROW>
</ROWS>$$作为数据；

选择xmltable.\*从xmldata，XMLTABLE（“//ROWS/ROW”传递数据列id int PATH'@id'，序数表示序数，“COUNTRY_NAME”文本，COUNTRY_id文本路径'COUNTRY_id'，size_sq_km浮动路径'size[@单位=“平方公里”"]'大小\_其他文本路径'concat（大小[@单位!="“平方公里”_]尺寸[@单位!="“平方公里”_]/@单元），premier_name文本路径“premier_name”默认值“未指定”）；

id |普通性|国家|名称|国家| id |大小|平方公里|大小|其他|总理|名称

### 9.15.4.将表映射到XML

[](<>)

以下函数将关系表的内容映射为XML值。它们可以被视为XML导出功能：

```
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
```

`表_to _xml`映射作为参数传递的命名表的内容*`桌子`*这个`regclass`类型接受使用常用符号标识表的字符串，包括可选的模式限定和双引号（请参阅[第8.19节](datatype-oid.html)详细信息）。`查询到xml`执行其文本作为参数传递的查询*`查询`*并映射结果集。`光标指向xml`从参数指定的游标中获取指定的行数*`光标`*。如果必须映射大型表，建议使用此变量，因为每个函数都会在内存中建立结果值。

如果*`台地森林`*如果为false，则生成的XML文档如下所示：

```
<tablename>
  <row>
    <columnname1>data</columnname1>
    <columnname2>data</columnname2>
  </row>

  <row>
    ...
  </row>

  ...
</tablename>
```

如果*`台地森林`*如果为true，则结果是一个XML内容片段，如下所示：

```
<tablename>
  <columnname1>data</columnname1>
  <columnname2>data</columnname2>
</tablename>

<tablename>
  ...
</tablename>

...
```

如果没有可用的表名，即在映射查询或光标时，字符串`桌子`在第一种格式中使用，`一行`第二种格式。

这些格式之间的选择取决于用户。第一种格式是正确的XML文档，这在许多应用程序中都很重要。第二种格式在未来更有用`光标指向xml`如果以后要将结果值重新组合到一个文档中，则该函数将起作用。特别是上面讨论的用于生成XML内容的函数`xmlelement`，可用于根据口味改变结果。

数据值的映射方式与函数中描述的相同`xmlelement`在上面

参数*`空值`*确定输出中是否应包含空值。如果为true，则列中的空值表示为：

```
<columnname xsi:nil="true"/>
```

哪里`南印第安湖`是XML架构实例的XML命名空间前缀。将向结果值中添加适当的命名空间声明。如果为false，则只需从输出中忽略包含null值的列。

参数*`目标`*指定结果所需的XML命名空间。如果不需要特定的名称空间，则应传递空字符串。

以下函数返回描述上述相应函数执行的映射的XML模式文档：

```
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模式文档，必须传递相同的参数。

以下函数在一个文档（或林）中生成XML数据映射和相应的XML模式，并链接在一起。在需要自包含和自描述的结果时，它们可能很有用：

```
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_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
```

这些函数忽略当前用户无法读取的表。此外，数据库范围的函数会忽略当前用户没有的模式`用法`（查找）的特权。

请注意，这些操作可能会产生大量数据，这些数据需要存储在内存中。当请求大型模式或数据库的内容映射时，可能值得考虑单独映射表，甚至可能通过光标映射。

架构内容映射的结果如下所示：

```
<schemaname>

table1-mapping

table2-mapping

...

</schemaname>
```

其中，表映射的格式取决于*`台地森林`*参数，如上所述。

数据库内容映射的结果如下所示：

```
<dbname>

<schema1name>
  ...
</schema1name>

<schema2name>
  ...
</schema2name>

...

</dbname>
```

其中模式映射如上所述。

作为使用这些函数产生的输出的示例，[例9.1](functions-xml.html#XSLT-XML-HTML)显示一个XSLT样式表，用于转换`表_to _xml _和_xmlschema`指向包含表格数据格式副本的HTML文档。以类似的方式，这些函数的结果可以转换为其他基于XML的格式。

**例9.1。用于将SQL/XML输出转换为HTML的XSLT样式表**

```
<?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>
```