Functions of the XML Type
In all the following XML functions, when the GUC parameter xmloption is set to content, the value of encoding in the XML declaration can be ZHS16GBK. When the GUC parameter xmloption is set to document, the value of encoding in the XML declaration cannot be ZHS16GBK. If encoding is set to ZHS16GBK, an error is reported.
- xmlparse ( { DOCUMENT | CONTENT } value [wellformed])
Description: Generates XML values from character data.
Parameter: data of the TEXT type
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
gaussdb=# SELECT XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>'); xmlparse ---------------------------------------------------------- <book><title>Manual</title><chapter>...</chapter></book> (1 row) gaussdb=# SELECT XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>'); xmlparse --------------------------------- abc<foo>bar</foo><bar>foo</bar> (1 row) gaussdb=# SELECT XMLPARSE (CONTENT 'abc<foo>bar</foo' wellformed); xmlparse ------------------ abc<foo>bar</foo (1 row)
- xmlserialize( { DOCUMENT | CONTENT } value AS type )
Description: Generates a string from an XML file.
Parameter: The data type can be character, character varying, text, or any variant of them.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10
gaussdb=# SELECT xmlserialize(CONTENT 'good' AS CHAR(10)); xmlserialize -------------- good (1 row) gaussdb=# SELECT xmlserialize(DOCUMENT '<head>bad</head>' as text); xmlserialize ------------------ <head>bad</head> (1 row)
If a string value is converted to XML without using the xmlparse or xmlserialize function, the XML OPTION session parameter determines the value, DOCUMENT or CONTENT. The XML OPTION session parameter can be set by the standard command.1SET XML OPTION { DOCUMENT | CONTENT };
Or use similar syntax to set this parameter.
1SET xmloption TO { DOCUMENT | CONTENT };
- xmlcomment(text)
Description: Creates an XML value that contains an XML comment with the specified text as the content. The text does not contain the "--" character and does not end with a "-" character. Besides, the text should meet the format requirements of XML comments. If the parameter is empty, the result is also empty.
Parameter: data of the TEXT type
Return type: XML
Example:
1 2 3 4
gaussdb=# SELECT xmlcomment('hello'); xmlcomment -------------- <!--hello-->
- xmlconcat(xml[, ...])
Description: Concatenates a list of single XML values into a single value that contains an XML content fragment. Null values are ignored, and the result is null only when all parameters are null. In ORA-compatible mode, you can set a_format_version to 10c and a_format_dev_version to s2 to check whether the input segment is well-formed XML text.
Parameter: data of the XML type.
Return type: XML
Example 1:
gaussdb=# SET xmloption=content; SET gaussdb=# SELECT XMLCONCAT(('<?xml version="1.0" encoding="GB2312" standalone="no"?><bar>foo</bar>'),('<?xml version="1.0" encoding="GB2312" standalone="no" ?><bar>foo</bar>')) ; xmlconcat ------------------------------------------------------------------- <?xml version="1.0" standalone="no"?><bar>foo</bar><bar>foo</bar> (1 row) gaussdb=# SELECT XMLCONCAT('abc>'); xmlconcat ----------- abc> (1 row)
Example 2: Syntax of the ORA-compatible database
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=s2; SET gaussdb_ora=# SET xmloption=content; SET gaussdb_ora=# SELECT XMLCONCAT(('<?xml version="1.0" encoding="GB2312" standalone="no"?><bar>foo</bar>'),('<?xml version="1.0" encoding="GB2312" standalone="no" ?><bar>foo</bar>')) ; xmlconcat ------------------------------------------------------------------- <?xml version="1.0" standalone="no"?><bar>foo</bar><bar>foo</bar> (1 row) gaussdb_ora=# SELECT XMLCONCAT('abc>'); ERROR: invalid XML document DETAIL: line 1: Start tag expected, '<' not found abc> ^ CONTEXT: referenced column: xmlconcat gaussdb_ora=# \c postgres gaussdb=# DROP DATABASE gaussdb_ora; DROP DATABASE
In ORA-compatible database, if a_format_version is set to 10c and a_format_dev_version is set to s2, and the encoding attribute in the XML declaration is set to ZHS16GBK, the XMLCONCAT function reports an error.
- xmlagg(xml [order_by_clause])
Description: Concatenates the input values called by the aggregate function. Cross-line concatenation is supported. For details about order_by_clause, see SELECT. In ORA-compatible database mode, you can set a_format_version to 10c and a_format_dev_version to s2. The xmloption parameter of the database is set to content by default. When xmloption is set to document, newline characters are used to concatenate multiple XML lines. If the encoding attribute value in the XML declaration is not the default UTF-8, the aggregation result contains the XML declaration.
Parameter: XML
Return type: XML
Example 1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
gaussdb=# CREATE TABLE xmltest ( id int, data xml ); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. gaussdb=# INSERT INTO xmltest VALUES (1, '<value>one</value>'); INSERT 0 1 gaussdb=# INSERT INTO xmltest VALUES (2, '<value>two</value>'); INSERT 0 1 gaussdb=# SELECT xmlagg(data) FROM xmltest; xmlagg -------------------------------------- <value>one</value><value>two</value> (1 row)
Example 2: Syntax of the ORA-compatible database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
gaussdb=# SET xmloption=document; SET gaussdb=# SELECT xmlagg(data) FROM xmltest; xmlagg -------------------- <value>one</value>+ <value>two</value> (1 row) gaussdb=# DELETE FROM XMLTEST; DELETE 2 gaussdb=# INSERT INTO xmltest VALUES (1, '<?xml version="1.0" encoding="GBK"?><value>one</value>'); INSERT 0 1 gaussdb=# INSERT INTO xmltest VALUES (2, '<?xml version="1.0" encoding="GBK"?><value>two</value>'); INSERT 0 1 gaussdb=# SELECT xmlagg(data) FROM xmltest; xmlagg -------------------------------------------------------- <?xml version="1.0" encoding="GBK"?><value>one</value>+ <value>two</value> (1 row) gaussdb=# SELECT xmlagg(data order by id desc) FROM xmltest; xmlagg -------------------------------------------------------- <?xml version="1.0" encoding="GBK"?><value>two</value>+ <value>one</value> (1 row) gaussdb=# DROP TABLE xmltest;
- xmlelement( [ ENTITYESCAPING | NOENTITYESCAPING ] { [ NAME ] element_name | EVALNAME element_name } [ , xmlattributes( [ ENTITYESCAPING | NOENTITYESCAPING ] value [ [ AS ] attname | AS EVALNAME attname ] [ , ... ] ) ] [ , content [ [ AS ] alias ] [ , ... ] ] )
Description: Generates an XML element with the given name, attribute, and content.
Return type: XML
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
gaussdb=# SELECT xmlelement(name foo); xmlelement ------------ <foo/> -- In ORA-compatible mode: 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=s2; SET -- If the keyword ENTITYESCAPING is not set in XMLElement by default or is set, the reserved characters in the content of XMLElement are escaped. gaussdb_ora=# SELECT xmlelement("entityescaping<>", 'a$><&"b'); xmlelement ------------------------------------------------------------- <entityescaping<>>a$><&"b</entityescaping<>> (1 row) gaussdb_ora=# SELECT xmlelement(entityescaping "entityescaping<>", 'a$><&"b'); xmlelement ------------------------------------------------------------- <entityescaping<>>a$><&"b</entityescaping<>> (1 row) -- When the keyword NOENTITYESCAPING is set in XMLElement, the reserved characters in the content of XMLElement will not be escaped. gaussdb_ora=# SELECT xmlelement(noentityescaping "entityescaping<>", 'a$><&"b'); xmlelement ---------------------------------------------- <entityescaping<>>a$><&"b</entityescaping<>> (1 row) -- When [AS] alias is used to declare an alias for the content in XMLElement, the content value type must be XML. gaussdb_ora=# SELECT xmlelement("entityescaping<>", '<abc/>' b); ERROR: argument of XMLELEMENT must be type xml, not type unknown LINE 1: SELECT xmlelement("entityescaping<>", '<abc/>' b); ^ CONTEXT: referenced column: xmlelement gaussdb_ora=# SELECT xmlelement("entityescaping<>", '<abc/>' as b); ERROR: argument of XMLELEMENT must be type xml, not type unknown LINE 1: SELECT xmlelement("entityescaping<>", '<abc/>' as b); ^ CONTEXT: referenced column: xmlelement gaussdb_ora=# SELECT xmlelement("entityescaping<>", xml('<abc/>') b); xmlelement --------------------------------------------- <entityescaping<>><abc/></entityescaping<>> (1 row) gaussdb_ora=# SELECT xmlelement("entityescaping<>", xml('<abc/>') as b); xmlelement --------------------------------------------- <entityescaping<>><abc/></entityescaping<>> (1 row) -- If the keyword ENTITYESCAPING is not set in XMLAttributes by default or is set, the reserved characters in XMLAttributes are escaped. gaussdb_ora=# SELECT xmlelement("entityescaping<>", xmlattributes('entityescaping<>' "entityescaping<>")); xmlelement --------------------------------------------------------------- <entityescaping<> entityescaping<>="entityescaping<>"/> (1 row) gaussdb_ora=# SELECT xmlelement(name "entityescaping<>", xmlattributes(entityescaping 'entityescaping<>' "entityescaping<>")); xmlelement --------------------------------------------------------------- <entityescaping<> entityescaping<>="entityescaping<>"/> (1 row) -- When the NOENTITYESCAPING keyword is set in XMLAttributes, the reserved characters in XMLAttributes will not be escaped. gaussdb_ora=# SELECT xmlelement("entityescaping<>", xmlattributes(noentityescaping 'entityescaping<>' "entityescaping<>")); xmlelement --------------------------------------------------------- <entityescaping<> entityescaping<>="entityescaping<>"/> (1 row) gaussdb_ora=# \c postgres gaussdb=# DROP DATABASE gaussdb_ora; DROP DATABASE
- For xmlelement and xmlattributes, when the value of name is NULL, the database behavior is different from that of the O database. When the name field of xmlelement is set to NULL, the name information is empty and the attribute information is not displayed. When the name field of xmlattributes is set to NULL, the attribute information is not displayed.
- After the following two parameters are set, the content escape rule of xmlelement is ORA compatible. If the two parameters are not set, the content escape rule of xmlelement is PG compatible.
1 2
SET a_format_version='10c'; SET a_format_dev_version=s2;
- xmlforest(content [AS name] [, ...])
Description: Generates an XML sequence of elements using the given name and content.
Return type: XML
Example:1 2 3 4
gaussdb=# SELECT xmlforest('abc' AS foo, 123 AS bar); xmlforest ------------------------------ <foo>abc</foo><bar>123</bar>
- xmlpi(name target [, content])
Description: Creates an XML processing instruction. If the content is not empty, the content cannot contain character collations.
Return type: XML
Example:1 2 3 4
gaussdb=# SELECT xmlpi(name php, 'echo "hello world";'); xmlpi ----------------------------- <?php echo "hello world";?>
- xmlroot(xml, version text | no value [, standalone yes|no|no value])
Description: Modifies the attributes of the root node of an XML value. If a version is specified, it replaces the value in the version declaration of the root node. If a standalone property is specified, it replaces the value of standalone declaration in the root node.
Example:1 2 3 4 5
gaussdb=# SELECT xmlroot('<?xml version="1.1"?><content>abc</content>',version '1.0', standalone yes); xmlroot -------------------------------------------------------------- <?xml version="1.0" standalone="yes"?><content>abc</content> (1 row)
- xmlexists(text passing [BY REF] xml [BY REF])
Description: Evaluates an XPath 1.0 expression (the first parameter) with the passed XML value as its context item. If the evaluation result generates an empty set of nodes, the function returns false. If any other value is generated, the function returns true. If the value of any parameter is null, the function returns Null. The non-null value passed as a context item must be an XML document, not a content fragment or any non-XML value.
Parameter: XML
Return type: Boolean
Example:1 2 3 4 5
gaussdb=# SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Toronto</town><town>Ottawa</town></towns>'); xmlexists ------------ t (1 row)
- xml_is_well_formed(text)
Description: Checks whether the text is in the correct XML format and returns a Boolean value.
Parameter: text
Return type: Boolean
Example:1 2 3 4 5
gaussdb=# SELECT xml_is_well_formed('<>'); xml_is_well_formed -------------------- f (1 row)
- xml_is_well_formed_document(text)
Description: Checks whether the text is in the correct XML format and returns a Boolean value.
Parameter: text
Return type: Boolean
Example:1 2 3 4 5
gaussdb=# 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)
- xml_is_well_formed_content(text)
Description: Checks whether the text is in the correct XML format and returns a Boolean value.
Parameter: text
Return type: Boolean
Example:1 2 3 4 5
gaussdb=# SELECT xml_is_well_formed_content('k'); xml_is_well_formed_content ---------------------------- t (1 row)
- xpath(xpath, xml [, nsarray])
Description: Calculates an XPath 1.0 expression, for example, xpath (a text value), on XML data. It returns an array of XML values corresponding to the node set generated 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 parameter must be a well formed XML document. It must have a single root node element.
The optional third parameter 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 (that is, 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), and the second element is 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 (that is, both in the XML document and in the XPath function context, aliases are local).
Return type: XML
Example:1 2 3 4 5
gaussdb=# 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)
- xpath_exists(xpath, xml [, nsarray])
Description: This function is a special form of the xpath function. It returns a Boolean indicating whether the query is satisfied or not (that is, whether it produces any value other than an empty node set), instead of returning the individual XML values that satisfy the XPath 1.0 expression. This function is equivalent to the standard XMLEXISTS predicate, but it also provides support for a namespace mapping parameter.
Return type: Boolean
Example:1 2 3 4 5
gaussdb=# 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)
The following XML function examples show the data you need to prepare:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
gaussdb=# CREATE SCHEMA testxmlschema; CREATE SCHEMA gaussdb=# CREATE TABLE testxmlschema.test1 (a int, b text); CREATE TABLE gaussdb=# INSERT INTO testxmlschema.test1 VALUES (1, 'one'), (2, 'two'), (-1, null); INSERT 0 3 gaussdb=# CREATE DATABASE test; CREATE DATABASE -- After the example is executed, run the following commands to delete the preceding data: gaussdb=# DROP DATABASE test; DROP DATABASE gaussdb=# DROP TABLE testxmlschema.test1; DROP TABLE gaussdb=# DROP SCHEMA testxmlschema; DROP SCHEMA
- query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of a query to an XML schema document.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
gaussdb=# SELECT query_to_xml('SELECT * FROM testxmlschema.test1', false, false, ''); query_to_xml --------------------------------------------------------------- <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+ + <row> + <a>1</a> + <b>one</b> + </row> + + <row> + <a>2</a> + <b>two</b> + </row> + + <row> + <a>-1</a> + </row> + + </table> + (1 row)
- query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of a query to an XML document and an XML schema document, and joins the two documents together.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
gaussdb=# SELECT query_to_xmlschema('SELECT * FROM testxmlschema.test1', false, false, ''); query_to_xmlschema ---------------------------------------------------------------------------------------------- <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema"> + + <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"> + <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"> + <xsd:sequence> + <xsd:element name="row" type="RowType" minOccurs="0" maxOccurs="unbounded"/> + </xsd:sequence> + </xsd:complexType> + + <xsd:element name="table" type="TableType"/> + + </xsd:schema> (1 row)
- query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of a query to an XML document and an XML schema document, and joins the two documents together.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
gaussdb=# SELECT query_to_xml_and_xmlschema('SELECT * FROM testxmlschema.test1', true, true, ''); query_to_xml_and_xmlschema ------------------------------------------------------------------------------------------------ <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema"> + + <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"> + <xsd:sequence> + <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> + <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+ </xsd:sequence> + </xsd:complexType> + + <xsd:element name="row" type="RowType"/> + + </xsd:schema> + + <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> + + <a>1</a> + <b>one</b> + </row> + + <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> + + <a>2</a> + <b>two</b> + </row> + + <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> + + <a>-1</a> + <b xsi:nil="true"/> + </row> + + (1 row)
- cursor_to_xml(cursor refcursor, count int, nulls boolean,tableforest boolean, targetns text)
Description: This function maps the contents of a cursor to an XML document.
Return type: XML
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
gaussdb=# CURSOR xc WITH HOLD FOR SELECT * FROM testxmlschema.test1 ORDER BY 1, 2; DECLARE CURSOR gaussdb=# SELECT cursor_to_xml('xc'::refcursor, 5, false, true, ''); cursor_to_xml ------------------------------------------------------------- <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+ + <a>-1</a> + </row> + + <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+ + <a>1</a> + <b>one</b> + </row> + + <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+ + <a>2</a> + <b>two</b> + </row> + + (1 row)
- cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of a cursor to an XML schema document.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
gaussdb=# SELECT cursor_to_xmlschema('xc'::refcursor, true, false, ''); cursor_to_xmlschema ------------------------------------------------------------------------------------------------ <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema"> + + <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"> + <xsd:sequence> + <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> + <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+ </xsd:sequence> + </xsd:complexType> + + <xsd:complexType name="TableType"> + <xsd:sequence> + <xsd:element name="row" type="RowType" minOccurs="0" maxOccurs="unbounded"/> + </xsd:sequence> + </xsd:complexType> + + <xsd:element name="table" type="TableType"/> + + </xsd:schema> (1 row)
- schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of the entire schema to an XML document.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
gaussdb=# SELECT schema_to_xml('testxmlschema', false, true, ''); schema_to_xml ----------------------------------------------------------------------- <testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+ + <test1> + + <a>1</a> + <b>one</b> + </test1> + + <test1> + + <a>2</a> + <b>two</b> + </test1> + + <test1> + + <a>-1</a> + </test1> + + + </testxmlschema> + (1 row)
- schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of the entire schema to an XML schema document.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
gaussdb=# SELECT schema_to_xmlschema('testxmlschema', false, true, ''); schema_to_xmlschema ----------------------------------------------------------------------------------------------------------- <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema"> + + <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.t1.pg_catalog.text"> + <xsd:restriction base="xsd:string"> + </xsd:restriction> + </xsd:simpleType> + + <xsd:complexType name="SchemaType.t1.testxmlschema"> + <xsd:sequence> + <xsd:element name="test1" type="RowType.t1.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+ </xsd:sequence> + </xsd:complexType> + + <xsd:element name="testxmlschema" type="SchemaType.t1.testxmlschema"/> + + </xsd:schema> (1 row)
- schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of the entire schema to an XML document and an XML schema document, and joins the two documents together.
Return type: XML
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
gaussdb=# SELECT schema_to_xml_and_xmlschema('testxmlschema', true, true, 'foo'); schema_to_xml_and_xmlschema -------------------------------------------------------------------------------------------------------------- <testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo" xsi:schemaLocation="foo #">+ + + <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema" + targetNamespace="foo" + elementFormDefault="qualified"> + + <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.t1.pg_catalog.text"> + <xsd:restriction base="xsd:string"> + </xsd:restriction> + </xsd:simpleType> + + <xsd:complexType name="SchemaType.t1.testxmlschema"> + <xsd:sequence> + <xsd:element name="test1" type="RowType.t1.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/> + </xsd:sequence> + </xsd:complexType> + + <xsd:element name="testxmlschema" type="SchemaType.t1.testxmlschema"/> + + </xsd:schema> + + <test1> + + <a>1</a> + <b>one</b> + </test1> + + <test1> + + <a>2</a> + <b>two</b> + </test1> + + <test1> + + <a>-1</a> + <b xsi:nil="true"/> + </test1> + + + </testxmlschema> + (1 row)
- database_to_xml(nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of the entire database to an XML document.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
gaussdb=# SELECT database_to_xml(true, true, 'test'); database_to_xml --------------------------------------------------------------------------- <test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="test">+ + <dbe_x005F_xml> + + </dbe_x005F_xml> + + <dbe_x005F_xmldom> + + </dbe_x005F_xmldom> + + <dbe_x005F_xmlparser> + + </dbe_x005F_xmlparser> + + <public> + + </public> + + </test> + (1 row)
- database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of the entire database to an XML schema document.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
gaussdb=# SELECT database_to_xmlschema(true, true, 'test'); database_to_xmlschema ------------------------------------------------------------------------------------------ <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema" + 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)
Description: This function maps the contents of the entire schema to an XML document and an XML schema document, and joins the two documents together.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
gaussdb=# SELECT database_to_xml_and_xmlschema(true, true, 'test'); database_to_xml_and_xmlschema ------------------------------------------------------------------------------------------------------- <test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="test" xsi:schemaLocation="test #">+ + <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema" + 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> + + <public> + + </public> + + </test> + (1 row)
- table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of a relational table to an XML document.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
gaussdb=# SELECT table_to_xml('testxmlschema.test1', false, false, ''); table_to_xml --------------------------------------------------------------- <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+ + <row> + <a>1</a> + <b>one</b> + </row> + + <row> + <a>2</a> + <b>two</b> + </row> + + <row> + <a>-1</a> + </row> + + </test1> + (1 row)
- table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
Description: This function maps the contents of a relational table to an XML schema document.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
gaussdb=# SELECT table_to_xmlschema('testxmlschema.test1', false, false, ''); table_to_xmlschema ----------------------------------------------------------------------------------------------------------------- <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema"> + + <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)
Description: This function maps the contents of a relational table to an XML document and an XML schema document, and joins the two documents together.
Return type: XML
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
gaussdb=# SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, false, ''); table_to_xml_and_xmlschema ----------------------------------------------------------------------------------------------------------------- <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="#"> + + <xsd:schema + xmlns:xsd="http://www.w3.org/2001/XMLSchema"> + + <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> + + <row> + <a>1</a> + <b>one</b> + </row> + + <row> + <a>2</a> + <b>two</b> + </row> + + <row> + <a>-1</a> + </row> + + </test1> + (1 row)
- For XPath-related functions, only XPath() and XPath_exists() are supported. These functions use the XPath language to query XML files and depend on the Libxml2 library, which is provided only in XPath1.0. Therefore, only XPath 1.0 is supported.
- The XQuery, XML extension, and XSLT functions are not supported.
- getclobval(xml)
Description: Converts an XML type to a CLOB type. This function is valid only when parameters a_format_version is set to 10c and a_format_dev_version is set to s4.
Parameter: The input parameter is of the XML type.
Return type: CLOB
Example:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
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 := '<a>123</a>'; result := getclobval(xmldata); RAISE NOTICE 'result is : %',result; END; / NOTICE: result is : <a>123</a> gaussdb_ora=# SELECT getclobval(xmlparse(document '<a>123</a>')); getclobval ------------ <a>123</a> (1 row) gaussdb_ora=# \c postgres gaussdb=# DROP DATABASE gaussdb_ora; DROP DATABASE
- getstringval(xml)
Description: Converts an XML type to a string. This function is valid only when a_format_version is set to 10c and a_format_dev_version is set to s4.
Parameter: The input parameter is of the XML type.
Return type: VARCHAR2
Example:
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)
Description: Converts an XML parameter into an array of the XMLTYPE type. Each array element is an XMLTYPE object. The input parameter of this function cannot be null and must be a valid XML document. If the input parameter does not meet the requirements, the function returns a null value or throws an exception. This function can be used to process multiple child nodes in an XML document or split an XML document into multiple fragments.
Parameter: XML
Return type: Array of the XMLType type.
Example: If you want to convert this document into an array containing three elements, each of which is a book node, use the following statement: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)
If an XML file contains double quotation marks, the result of the xmlsequence function contains escape characters of double quotation marks when you query the result of the xmlsequence function independently. The use of the xmlsequence function is not affected.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
