GaussDB-XML类型函数（下）

database_to_xml(nulls boolean, tableforest boolean, targetns text)

描述：该函数会将整个数据库的内容映射成XML文档。

返回值类型：XML

示例：

| ``` gaussdb=# SELECT database_to_xml(true, true, 'test'); database_to_xml --------------------------------------------------------------------------- + + <dbe_x005F_xml> + + </dbe_x005F_xml> + + <dbe_x005F_xmldom> + + </dbe_x005F_xmldom> + + <dbe_x005F_xmlparser> + + </dbe_x005F_xmlparser> + + + + + + + (1 row)

| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)

描述：该函数会将整个数据库的内容映射成XML模式文档。

返回值类型：XML

示例：

| ``` gaussdb=# SELECT database_to_xmlschema(true, true, 'test'); database_to_xmlschema ------------------------------------------------------------------------------------------ <xsd:schema + xmlns:xsd="www.w3.org/2001/XMLSch…" + targetNamespace="test" + elementFormDefault="qualified"> + + <xsd:complexType name="CatalogType.test"> + xsd:all + <xsd:element name="dbe_x005F_xml" type="SchemaType.test.dbe_x005F_xml"/> + <xsd:element name="dbe_x005F_xmldom" type="SchemaType.test.dbe_x005F_xmldom"/> + <xsd:element name="dbe_x005F_xmlparser" type="SchemaType.test.dbe_x005F_xmlparser"/>+ <xsd:element name="public" type="SchemaType.test.public"/> + </xsd:all> + </xsd:complexType> + + <xsd:element name="test" type="CatalogType.test"/> + + </xsd:schema> (1 row)

| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text)

描述：该函数会将整个模式的内容映射成XML文档和XML模式文档，并把两个文档连接在一起。

返回值类型：XML

示例：

| ``` gaussdb=# SELECT database_to_xml_and_xmlschema(true, true, 'test'); database_to_xml_and_xmlschema ------------------------------------------------------------------------------------------------------- + + <xsd:schema + xmlns:xsd="www.w3.org/2001/XMLSch…" + targetNamespace="test" + elementFormDefault="qualified"> + + <xsd:complexType name="CatalogType.test"> + xsd:all + <xsd:element name="dbe_x005F_xml" type="SchemaType.test.dbe_x005F_xml"/> + <xsd:element name="dbe_x005F_xmldom" type="SchemaType.test.dbe_x005F_xmldom"/> + <xsd:element name="dbe_x005F_xmlparser" type="SchemaType.test.dbe_x005F_xmlparser"/> + <xsd:element name="public" type="SchemaType.test.public"/> + </xsd:all> + </xsd:complexType> + + <xsd:element name="test" type="CatalogType.test"/> + + </xsd:schema> + + <dbe_x005F_xml> + + </dbe_x005F_xml> + + <dbe_x005F_xmldom> + + </dbe_x005F_xmldom> + + <dbe_x005F_xmlparser> + + </dbe_x005F_xmlparser> + + + + + + + (1 row)

| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)

描述：该函数会将关系表的内容映射成XML文档。

返回值类型：XML

示例：

| ``` gaussdb=# SELECT table_to_xml('testxmlschema.test1', false, false, ''); table_to_xml --------------------------------------------------------------- + + + 1 + one + + + + 2 + two + + + + -1 + + + + (1 row)

| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)

描述：该函数会将关系表的内容映射成XML模式文档。

返回值类型：XML

示例：

| ``` gaussdb=# SELECT table_to_xmlschema('testxmlschema.test1', false, false, ''); table_to_xmlschema ----------------------------------------------------------------------------------------------------------------- <xsd:schema + xmlns:xsd="www.w3.org/2001/XMLSch… + + <xsd:simpleType name="INTEGER"> + <xsd:restriction base="xsd:int"> + <xsd:maxInclusive value="2147483647"/> + <xsd:minInclusive value="-2147483648"/> + </xsd:restriction> + </xsd:simpleType> + + <xsd:simpleType name="UDT.regression.pg_catalog.text"> + <xsd:restriction base="xsd:string"> + </xsd:restriction> + </xsd:simpleType> + + <xsd:complexType name="RowType.regression.testxmlschema.test1"> + xsd:sequence + <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> + <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element> + </xsd:sequence> + </xsd:complexType> + + <xsd:complexType name="TableType.regression.testxmlschema.test1"> + xsd:sequence + <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+ </xsd:sequence> + </xsd:complexType> + + <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> + + </xsd:schema> (1 row)

| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)

描述：该函数会将关系表的内容映射成XML文档和XML模式文档，并把两个文档连接在一起。

返回值类型：XML

示例：

| ``` gaussdb=# SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, false, ''); table_to_xml_and_xmlschema ----------------------------------------------------------------------------------------------------------------- + + <xsd:schema + xmlns:xsd="www.w3.org/2001/XMLSch… + + <xsd:simpleType name="INTEGER"> + <xsd:restriction base="xsd:int"> + <xsd:maxInclusive value="2147483647"/> + <xsd:minInclusive value="-2147483648"/> + </xsd:restriction> + </xsd:simpleType> + + <xsd:simpleType name="UDT.regression.pg_catalog.text"> + <xsd:restriction base="xsd:string"> + </xsd:restriction> + </xsd:simpleType> + + <xsd:complexType name="RowType.regression.testxmlschema.test1"> + xsd:sequence + <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> + <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element> + </xsd:sequence> + </xsd:complexType> + + <xsd:complexType name="TableType.regression.testxmlschema.test1"> + xsd:sequence + <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+ </xsd:sequence> + </xsd:complexType> + + <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> + + </xsd:schema> + + + 1 + one + + + + 2 + two + + + + -1 + + + + (1 row)

| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

![](https://p9-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/9fe187d0fd2d4dc1b0ac5a7140d0ff25~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAgamVycnl3YW5nMTk4Mw==:q75.awebp?rk3s=f64ab15b&x-expires=1771811335&x-signature=llOhIvYd7AvtEA0BKcx%2Bi%2Fbksw4%3D)

-   xpath相关函数仅支持xpath()和xpath_exists()，由于其使用xpath语言查询XML文档，而这些函数都依赖于libxml2库，且这个库仅在Xpath1.0提供，所以对XPath的限制为1.0。
-   不支持xquery、xml extension、xslt功能。

getclobval(xml)

描述：将XML类型转化成CLOB类型。该函数在参数a_format_version值为10c和a_format_dev_version值为s4的情况下有效。

参数：入参为XML类型。

返回值类型：CLOB

示例：

| ``` gaussdb=# CREATE DATABASE gaussdb_ora DBCOMPATIBILITY='ORA'; CREATE DATABASE gaussdb=# \c gaussdb_ora gaussdb_ora=# SET a_format_version='10c'; SET gaussdb_ora=# SET a_format_dev_version='s4'; SET gaussdb_ora=# declare xmldata xml; result clob; begin xmldata := '123'; result := getclobval(xmldata); RAISE NOTICE 'result is : %',result; END; / NOTICE: result is : 123 gaussdb_ora=# SELECT getclobval(xmlparse(document '123')); getclobval ------------ 123 (1 row) gaussdb_ora=# \c postgres gaussdb=# DROP DATABASE gaussdb_ora; DROP DATABASE

| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

getstringval(xml)

描述：该函数将XML类型转化为字符串。此函数在参数a_format_version值为10c和a_format_dev_version值为s4的情况下有效。

参数：入参为XML类型。

返回值类型：VARCHAR2

示例：

gaussdb=# CREATE DATABASE gaussdb_ora DBCOMPATIBILITY='ORA';
CREATE DATABASE
gaussdb=# \c gaussdb_ora
gaussdb_ora=# SET a_format_version='10c';
SET
gaussdb_ora=# SET a_format_dev_version='s4';
SET
gaussdb_ora=# declare
 xmldata xml;
 result varchar2;
begin
 xmldata := '<a>123<b>456</b></a>';
 result := getstringval(xmldata);
 RAISE NOTICE 'result is : %',result;
END;
/
NOTICE:  result is : <a>123<b>456</b></a>
gaussdb_ora=# select getstringval(xmlparse(document '<a>123<b>456</b></a>'));
     getstringval
----------------------
 <a>123<b>456</b></a>
(1 row)

gaussdb_ora=# \c postgres
gaussdb=# DROP DATABASE gaussdb_ora;
DROP DATABASE

xmlsequence(xml)

描述：此函数的功能是将一个XML类型的参数转换为一个XMLTYPE类型的数组，每个数组元素都是XMLTYPE对象。这个函数的输入参数不能为空，也必须是一个有效的XML文档。如果输入参数不符合要求，函数会返回空值或者抛出异常。这个函数可以用于处理XML文档中的多个子节点，或者将XML文档分割为多个片段。

参数：XML类型。

返回值类型：xmltype类型的数组。

示例：如果想要将这个文档转换为一个包含三个元素的数组，每个元素都是一个book节点，可以使用以下语句。

gaussdb=# SELECT xmlsequence(xml('<books><book><title>The Catcher in the Rye</title><author>J.D. Salinger</author><year>1951</year></book><book><title>1984</title><author>George Orwell</author><year>1949</year></book><book><title>The Hitchhiker''s Guide to the Galaxy</title><author>Douglas Adams</author><year>1979</year></book></books>'));
                        xmlsequence                        
-----------------------------------------------------------
 {"<books>                                                +
     <book>                                               +
       <title>The Catcher in the Rye</title>              +
       <author>J.D. Salinger</author>                     +
       <year>1951</year>                                  +
     </book>                                              +
     <book>                                               +
       <title>1984</title>                                +
       <author>George Orwell</author>                     +
       <year>1949</year>                                  +
     </book>                                              +
     <book>                                               +
       <title>The Hitchhiker's Guide to the Galaxy</title>+
       <author>Douglas Adams</author>                     +
       <year>1979</year>                                  +
     </book>                                              +
   </books>"}
(1 row)

当XML文档中含有双引号时，单独查看xmlsequence函数，结果中会包含双引号的转义符，使用xmlsequence函数结果时不受影响。

更多详情请参考GaussDB 文档中心：doc.hcs.huawei.com/db/zh-cn/ga…