Help Center> GaussDB> Centralized_3.x> SQL Reference> Data Types> XML Type
Updated on 2024-05-07 GMT+08:00
View PDF

XML Type

The XML data type can be used to store Extensible Markup Language (XML) data. The internal format of XML is the same as that of the TEXT data type. Its advantage over storing XML data in a TEXT field is that, XML data supports standard XML operation functions based on LIBXML2 and XML standardization check.

The XML data type can store well-formed documents as defined by the XML standard, as well as content fragments, which are defined by referencing broader "DOCUMENT NODE" XQuery and XPath data models. Roughly speaking, this means that there can be more than one top-level element or character node in a content fragment. The expression XMLVALUE IS DOCUMENT can be used to evaluate whether a particular XML value is a complete document or just a document fragment.

The XML parser converts an XML document into an XML DOM object. The document object model (DOM) defines standard methods for accessing and manipulating documents. XML DOM defines standard methods for accessing and manipulating XML documents. XML DOM views XML documents as a tree structure. All elements can be accessed through the DOM tree. You can modify or delete their contents and create new elements. Elements, their text, and their attributes are considered as nodes.

The XML bottom layer uses the same data structure as the TEXT type for storage. The maximum size is 1 GB.

Example:

gaussdb=# CREATE TABLE xmltest ( id int, data xml ); 
gaussdb=# INSERT INTO xmltest VALUES (1, 'one');
gaussdb=# INSERT INTO xmltest VALUES (2, 'two'); 
gaussdb=# SELECT * FROM xmltest ORDER BY 1;
 id | data 
----+--------------------
1 | one 
2 | two 
(2 rows)
gaussdb=# SELECT xmlconcat(xmlcomment('hello'),
                            xmlelement(NAME qux, 'xml'),
                            xmlcomment('world'));
               xmlconcat                
----------------------------------------
 <!--hello--><qux>xml</qux><!--world-->
(1 row)
gaussdb=# DROP TABLE xmltest;
  • The XML type does not support the following operations:
    • Logical expressions AND, OR, and NOT
    • Input parameter of a system function that is used as a non-XML operation function
    • Used as a distribution key, partition key, level-2 partition key, foreign key, primary key, or unique constraint.
    • Implicit conversion related to XML, including the conversion between strings and the XML data type
    • Array expression, row expression, subquery expression, TABLE OF, and TABLE OF INDEX
    • Use columns of the XML data format as common indexes, unique indexes, global indexes, local indexes, and partial indexes.
    • Comparison expressions >, <, >=, <=, =, <>, !=, ^=, <=>, BETWEEN AND, IS DISTINCT FROM, and IS NOT DISTINCT FROM
    • Condition expressions DECODE, NULLIF, GREATEST, and LEAST
    • Used as DISTINCT, GROUP BY, or ORDER BY parameters
    • Aggregate functions including sum, max, min, avg, list_agg, corr, covar_pop, covar_samp, stddev, stddev_pop, stddev_samp, var_pop, var_samp, variance, bit_and, bit_or, bool_and, bool_or, every, regr_avgx, regr_avgy, regr_count, regr_intercept, regr_r2, regr_slope, regr_sxx, regr_sxy, regr_syy, rank, and spread
    • ODBC-related APIs with binding parameters
  • The XML type supports the following operations:
    • Physical backup and restoration
    • Comparison expressions IS NULL and IS NOT NULL
    • Condition expressions CASE and COALESCE
    • Global temporary tables and local temporary tables
    • Forcible type conversion
    • Expression indexes
    • Input XML values that comply with the XML standard
    • gs_dump and gs_restore
    • Parallel query, supporting the Astore and Ustore storage engines
    • Input parameters, output parameters, customized variables, and return values of a user-defined function
    • Input parameters, output parameters, customized variables, and return values of a stored procedure, as well as stored procedures that support autonomous transactions.
    • Character processing function quote_literal(string text) (explicitly converted to the character type) and quote_nullable(string text) (explicitly converted to the character type)
    • Aggregate functions count, array_agg, and checksum (explicitly converted to the character type), and string_agg (explicitly converted to the character type)
    • If addition, deletion, modification, and query of user-defined composite types involve XML types that are similar to XML columns in common tables, the composite types must be inserted and modified based on the XML syntax.
    • JDBC and ODBC operations on XML data types are supported. The SELECT, UPDATE, INSERT, and DELETE operations can be performed on an XML column. You can enter an XML value using the SQL syntax and use the getSQLXML method of the ResultSet class to obtain the XML value. JDBC-related APIs with binding parameters are supported. For example, you can use the setSQLXML method in the PreparedStatement preprocessing API and the getSQLXML(int columnIndex) method in the ResultSet execution result set API.

      In the API calling process, use the java.sql.SQLXML API class to construct an XML object, set the specified object type to Oid.XML, and send the type ID and XML value to the server. After obtaining the result returned from the server, call ResultSet.getString. Then, use the java.sql.SQLXML API class to construct an XML object based on the obtained character string. In this case, the system checks whether the content complies with the XML standard again. Therefore, you can also use ResultSet.getString to directly obtain the XML string object.

Parent topic: Data Types