PostgreSQL XML Functions and Operators

Few set of xml functions and operations are available in PostgreSQL for XML operations.

Let us examine those one by one

xmlcomment

Creates xml comment, text should not contain “–”

select xmlcomment(‘hello google’); → <!–hello google–>

select xmlcomment(null); → [null]

text should not contain “–” or “-“, see below example

postgres=# select xmlcomment('hello -- google');
ERROR:  invalid XML comment

xmlconcat

Concatenate two individual xml and create a final xml

select xmlconcat(‘<hello/>’,'<google/>’); → <hellogoogle>

Documentation says

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

xmlelement

The xmlelement expression produces an xml element with the given name, attributes, and content

select xmlelement(name google); → <google/>

select xmlelement(name google, xmlattributes(‘product’ as gmail)); → <google gmail=”product”/>

select xmlelement(name google, xmlattributes(‘product’ as gmail),’version 1.0′); → <google gmail=”product”>version 1.0></google>

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

xmlforest

The xmlforest produces an XML forest (sequence) of elements using the given names and content. Each name must be a simple identifier, while the content expressions can have any data type

select xmlforest(‘John’ as name, 42 as age, ’01-Jan-1980′ as dob);

→ <name>John</name><age>42</age><dob>01-Jan-1980</dob>

postgres=# select xmlforest(empno, empname) FROM emp;
                  xmlforest                   
----------------------------------------------
 <empno>10</empno><empname>Bill</empname>
 <empno>11</empno><empname>Solomon</empname>
 <empno>12</empno><empname>Susan</empname>
 <empno>13</empno><empname>Wendy</empname>
 <empno>14</empno><empname>Benjamin</empname>
 <empno>15</empno><empname>Tom</empname>
 <empno>16</empno><empname>Henry</empname>
 <empno>17</empno><empname>Robert</empname>
 <empno>18</empno><empname>Paul</empname>
(9 rows)

xmlpi 

The xmlpi 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 must not contain the character sequence ?>

SELECT xmlpi(name php, ‘echo “hello world”;’); → <?php echo “hello world”;?>

xmlroot

xmlroot ( xml, VERSION {text|NO VALUE} [, STANDALONE {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

SELECT xmlroot(xmlparse(document ‘<?xml version=”1.1″?><content>abc</content>’), version ‘1.0’,standalone yes);

→ <?xml version=”1.0″ standalone=”yes”?> <content>abc</content>

xmlagg

xmlagg ( xml ) → xml

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.

postgres=# CREATE TABLE test (y int, x xml);
postgres=# INSERT INTO test VALUES (1, '<foo>abc</foo>');
postgres=# INSERT INTO test VALUES (2, '<bar/>');
postgres=# SELECT xmlagg(x) FROM test;
        xmlagg        
----------------------
 <foo>abc</foo><bar/>
(1 row)
postgres=# SELECT xmlagg(x ORDER BY y DESC) FROM test;
        xmlagg        
----------------------
 <bar/><foo>abc</foo>
(1 row)

XML Predicates/Operators

is documentselect ‘<google/>’ is document; → true
select ‘google’ is document; → false
is not documentselect ‘<google/> is not document; → false
select ‘google’ is not document; → true
xml_is_well_formedselect xml_is_well_formed(‘google’); → false
select xml_is_well_formed(‘<google/>’); → true

xpath

xpath ( xpath text, xml xml [, nsarray text[] ] ) → 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.

SELECT xpath(‘/my:a/text()’, ‘<my:a xmlns:my=”http://example.com”>test</my:a>&#8217;,ARRAY[ARRAY[‘my’, ‘http://example.com’%5D%5D); → {test}

xpath_exists

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

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

SELECT xpath_exists(‘/my:a/text()’, ‘<my:a xmlns:my=”http://example.com”>test</my:a>&#8217;,ARRAY[ARRAY[‘my’, ‘http://example.com’%5D%5D);

→ true

xmltable

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

CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
<ROW id="1">
<EMPNAME>John</EMPNAME>
<EMPAGE>35</EMPAGE>
</ROW>
<ROW id="2">
<EMPNAME>Peter</EMPNAME>
<EMPAGE>45</EMPAGE>
</ROW>
<ROW id="3">
<EMPNAME>Scott</EMPNAME>
<EMPAGE>30</EMPAGE>
</ROW>
</ROWS>
$$ AS data;

postgres=# select * from xmldata;
           data           
--------------------------
 <ROWS>                  +
 <ROW id="1">            +
 <EMPNAME>John</EMPNAME> +
 <EMPAGE>35</EMPAGE>     +
 </ROW>                  +
 <ROW id="2">            +
 <EMPNAME>Peter</EMPNAME>+
 <EMPAGE>45</EMPAGE>     +
 </ROW>                  +
 <ROW id="3">            +
 <EMPNAME>Scott</EMPNAME>+
 <EMPAGE>30</EMPAGE>     +
 </ROW>                  +
 </ROWS>                 +
 
(1 row)
postgres=# select xmltable.* FROM xmldata, XMLTABLE('//ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
ordinality FOR ORDINALITY,
"EMPNAME" text,
"EMPAGE" int,
EMPDOB text DEFAULT 'not specified'
);

id | ordinality | EMPNAME | EMPAGE |    empdob     
----+------------+---------+--------+---------------
  1 |          1 | John    |     35 | not specified
  2 |          2 | Peter   |     45 | not specified
  3 |          3 | Scott   |     30 | not specified
(3 rows)

table_to_xml

converts table to xml string

table_to_xml ( table regclass, nulls boolean, tableforest boolean, targetns text ) → xml

postgres=# select * from emp;
 empno | empname  | salary |    dob     | deptno 
-------+----------+--------+------------+--------
    10 | Bill     |  12000 | 1977-01-16 |      5
    11 | Solomon  |  10000 | 1964-06-25 |      5
    12 | Susan    |  10000 | 1982-02-26 |      5
    13 | Wendy    |   9000 | 1955-03-10 |      1
    14 | Benjamin |   7500 | 1980-02-28 |      1
    15 | Tom      |   7600 | 1969-01-09 |      1
    16 | Henry    |   8500 | 1972-04-19 |      2
    17 | Robert   |   9500 | 1979-05-31 |      2
    18 | Paul     |   7700 | 1960-07-04 |      2
(9 rows)

postgres=# SELECT table_to_xml('emp', true, false, '');

                       table_to_xml                         
-------------------------------------------------------------
 <emp xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
                                                            +
 <row>                                                      +
   <empno>10</empno>                                        +
   <empname>Bill</empname>                                  +
   <salary>12000</salary>                                   +
   <dob>1977-01-16</dob>                                    +
   <deptno>5</deptno>                                       +
 </row>                                                     +
                                                            +
 <row>                                                      +
   <empno>11</empno>                                        +
   <empname>Solomon</empname>                               +
   <salary>10000</salary>                                   +
   <dob>1964-06-25</dob>                                    +
   <deptno>5</deptno>                                       +
 </row>                                                     +
                                                            +
 <row>                                                      +
   <empno>12</empno>                                        +
   <empname>Susan</empname>                                 +
   <salary>10000</salary>                                   +
   <dob>1982-02-26</dob>                                    +
   <deptno>5</deptno>                                       +
 </row>                                                     +
                                                            +
 <row>                                                      +
   <empno>13</empno>                                        +
   <empname>Wendy</empname>                                 +
   <salary>9000</salary>                                    +
   <dob>1955-03-10</dob>                                    +
   <deptno>1</deptno>                                       +
 </row>                                                     +
                                                            +
 <row>                                                      +
   <empno>14</empno>                                        +
   <empname>Benjamin</empname>                              +
  <salary>7500</salary>                                    +
   <dob>1980-02-28</dob>                                    +
   <deptno>1</deptno>                                       +
 </row>                                                     +
                                                            +
 <row>                                                      +
   <empno>15</empno>                                        +
   <empname>Tom</empname>                                   +
   <salary>7600</salary>                                    +
   <dob>1969-01-09</dob>                                    +
   <deptno>1</deptno>                                       +
 </row>                                                     +
                                                            +
 <row>                                                      +
   <empno>16</empno>                                        +
   <empname>Henry</empname>                                 +
   <salary>8500</salary>                                    +
   <dob>1972-04-19</dob>                                    +
   <deptno>2</deptno>                                       +
 </row>                                                     +
                                                            +
 <row>                                                      +
   <empno>17</empno>                                        +
   <empname>Robert</empname>                                +
   <salary>9500</salary>                                    +
   <dob>1979-05-31</dob>                                    +
   <deptno>2</deptno>                                       +
 </row>                                                     +
                                                            +
 <row>                                                      +
   <empno>18</empno>                                        +
   <empname>Paul</empname>                                  +
   <salary>7700</salary>                                    +
   <dob>1960-07-04</dob>                                    +
   <deptno>2</deptno>                                       +
 </row>                         
 </emp>                                                     +
 
(1 row)

query_to_xml

converts query to xml

query_to_xml ( query text, nulls boolean, tableforest boolean, targetns text ) → xml

postgres=# SELECT query_to_xml('select * from emp limit 2', true, false, '');
                         query_to_xml                          
---------------------------------------------------------------
 <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
                                                              +
 <row>                                                        +
   <empno>10</empno>                                          +
   <empname>Bill</empname>                                    +
   <salary>12000</salary>                                     +
   <dob>1977-01-16</dob>                                      +
   <deptno>5</deptno>                                         +
 </row>                                                       +
                                                              +
 <row>                                                        +
   <empno>11</empno>                                          +
   <empname>Solomon</empname>                                 +
   <salary>10000</salary>                                     +
   <dob>1964-06-25</dob>                                      +
   <deptno>5</deptno>                                         +
 </row>                                                       +
                                                              +
 </table>                                                     +
 
(1 row)

cursor_to_xml

converts cusrsor to xml

cursor_to_xml ( cursor refcursor, count integer, nulls boolean, tableforest boolean, targetns text ) → xml

In addition, few more mapping functions are available in PostgreSQL

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: