CREATE EXTENSION xnode;
SET search_path TO xml;
CREATE TABLE STATES (
id int not null,
data doc not null
);
CREATE TABLE PATHS (
id int,
name varchar(32),
path xml.path not null
);
CREATE TABLE DOCS_OTHER (
id int,
data doc not null
);
-- Random geografic data
-- Czech and German languages are both good to test handling of multi-byte characters
INSERT INTO STATES VALUES(1, '
Cestina
');
INSERT INTO STATES VALUES(2, '
Deutsch
');
INSERT INTO STATES VALUES(3, '
Deutsch
');
update states set data=xml.add(data, '/state/languages', '', 'b') where id=2;
update states set data=add(data, '/state/languages', '', 'b') where id=2;
update states set data=add(data, '/state/region[@name="Saarland"]', '', 'i') where id=2;
update states set data=add(data, '/state/region[@name="Saarland"]', '', 'i') where id=2;
update states set data=add(data, '/state/region[@name="Saarland"]/city[@name="Saarbrücken"]', '', 'a') where id=2;
-- Simply dump the XML documents
select *
from states
order by id;
id | data
----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | +
| +
| +
| +
| +
| http://en.wikipedia.org/wiki/Lipno_Dam +
| Cestina +
|
2 | +
| +
| +
| +
| +
| +
| Deutsch+
|
3 | +
| +
| +
| +
| +
| +
| Deutsch +
|
(3 rows)
-- Query some XML paths
select path('/state/@name', data) as state, path('/state/@tld', data) as domain,
path('/state/languages/language/text()', data) as language
from states
order by id;
state | domain | language
----------------------------+--------+----------
Česká republika | cz | Cestina
Bundesrepublik Deutschland | de | Deutsch
Österreich | at | Deutsch
(3 rows)
select id, path('count(//city)', data)
from states
order by id;
id | path
----+------
1 | 3
2 | 8
3 | 4
(3 rows)
with paths(name, area, population)
as (select '/state/@name'::xml.path, '/state/@area'::xml.path, '/state/@population'::xml.path)
select id, path(p.name, data) as name, path(p.area, data) as area,
path(p.population, data) as population
from states s, paths p
order by s.id;
id | name | area | population
----+----------------------------+--------+------------
1 | Česká republika | 78866 | 10674947
2 | Bundesrepublik Deutschland | 357021 | 81757600
3 | Österreich | 83872 | 8356707
(3 rows)
-- The XML path expressions can also be parsed (preprocessed) and stored into a table for later use...
insert into paths (name, path) values ('names', '/state/@name'), ('capitals', '/state/city[@capital="true"]/@name'), ('regions', '/state/region'), ('region_capitals', '/region/city[@capital="true"]/@name');
-- ... and used
select path(pn.path, s.data) as name, path(pc.path, s.data) as capital
from states s
join paths pn on (pn.name='names')
join paths pc on (pc.name='capitals')
order by s.id;
name | capital
----------------------------+---------
Česká republika | Praha
Bundesrepublik Deutschland | Berlin
Österreich | Wien
(3 rows)
select s.id, path(p.path, data)
from states s
join paths p on (p.name='regions')
order by s.id;
id | path
----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | http://en.wikipedia.org/wiki/Lipno_Dam
2 |
3 |
(3 rows)
-- Convert data to table-like form
select id, path('/state', '{"region/city", "@area", "@population", "@tld"}', data) as state_info
from states
order by id;
id | state_info
----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | {NULL,78866,10674947,cz}
2 | {"",357021,81757600,de}
3 | {"",83872,8356707,at}
(3 rows)
-- Replace node
update states set data=add(data, '/state/region[@name="Šumava"]/destination', '', 'r') where id=1;
-- Add document fragment
-- before:
update states set data=add(data, '/state/languages', '', 'b') where id=1;
-- after:
update states set data=add(data, '/state/city[@name="Praha"]', '', 'a') where id=1;
-- into:
update states set data=add(data, '/state/city[@name="Praha"]', '', 'i') where id=1;
-- into empty element (special case):
update states set data=add(data, '/state', '', 'i') where id=1;
update states set data=add(data, '/state/people', '', 'i') where id=1;
-- replace:
update states set data=add(data, '/state/languages/language', 'ČeštinaHantec', 'r') where id=1;
select data
from states
where id=1
order by id;
data
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
+
+
+
+
+
ČeštinaHantec+
(1 row)
-- remove node:
update states set data=remove(data, '/state/city[@name="Kroměříž"]') where id=1;
-- ... and test if node will be correctly displayed as an empty element when the only child is removed:
update states set data=remove(data, '/state/city[@name="Plzeň"]/part[@name="Doubravka"]') where id=1;
select data
from states
where id=1
order by id;
data
-------------------------------------------------------------------------------------------------------------------------------------------------------------
+
+
+
+
+
+
ČeštinaHantec +
(1 row)
-- remove() - repeated occurrence of the target path:
update states set data=remove(data, '/state/text()');
select data
from states
order by id;
data
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ČeštinaHantec
Deutsch
Deutsch
(3 rows)
-- Additional tests
-- add() - repeated occurrence of the target path:
select add('', '/a/b', '', 'b');
add
---------------------------------------
(1 row)
select add('', '/a/b', '', 'a');
add
---------------------------------------
(1 row)
select add('', '/a/b', '', 'r');
add
-------------------------------
(1 row)
-- insert into both empty and non-empty element
select add('', '/a/b', '', 'i');
add
---------------------------------------------
(1 row)
select add('', '/a/b', '', 'i');
add
-----------------------------------------------------
(1 row)
select add('', '/a/b', '', 'i');
add
-----------------------------------------------------------
(1 row)
-- and the same for document fragment:
select add('', '/a/b', '', 'b');
add
-------------------------------------------------------------------------
(1 row)
select add('', '/a/b', '', 'a');
add
-------------------------------------------------------------------------
(1 row)
select add('', '/a/b', '', 'r');
add
-----------------------------------------------------------------
(1 row)
select add('', '/a/b', '', 'i');
add
-------------------------------------------------------------------------------
(1 row)
select add('', '/a/b', '', 'i');
add
---------------------------------------------------------------------------------------
(1 row)
select add('', '/a/b', '', 'i');
add
---------------------------------------------------------------------------------------------
(1 row)
-- remove() - repeated occurrence of the target path:
-- remove attribute
select remove('', '/a/b/@id');
remove
-----------------------------
(1 row)
-- remove the last attribute
select remove('', '/a/b/@id');
remove
-----------------
(1 row)
-- remove the last nested node(s)
select remove('', '/a/b');
remove
--------
(1 row)
-- test the XPath parser
delete from paths;
insert into paths (id, path) values (0, '/c[@cd != @e != "a" and @b and @c or @f]/@g'),
(1, '/a[@a or @cd != @e != "a" and @b=@k=@l or @s]/b/text()'),
(2, '/state[@tld or "cz" = "cz" and @tld and @a or @b]'),
(3, '/state[@tld="de" or @area="78866" and @population="1067494"]'),
(4, '/a[(@b != "a" and @k or @l)]/b/text()'),
(5, '/a[(@b != "a" and (@k or @l))]/b/text()'),
(6, '/a[(( @cd and @e) != "a" and @k)]/b[(@a > @c<=@g=@h)=i and @k=l]/text()'),
(7, '/a[@a or ((@e) != "a" = @k or @l)]/b/text()'),
-- ... don't forget to use (predicate) subpaths
(8, '/a[ab/cd/@a!=b=c/a[@a=@b=a[@c and (@b or @c)]] = @a]'),
-- check if 'npaths' is propagated from subexpressions (both explicit and implicit)
(9, '/a[@a and @b or (@c=a[@e=@f])]'),
(10, '/a[@a and @b or (@c=a[@e=f])]'),
(11, '/a[@a and @b or @c=a[@e=@f]]'),
(12, '/a[@a and @b or @c=a[@e=f]]'),
-- Absolute sub-path
(13, '//root[/a and //b or c]'),
-- Functions
(14, '/root/a[position()=position()]'),
(15, '/root/b[count(a)>=count(c)]'),
(16, '/root[contains("", a)]');
select id, path
from paths
order by id asc;
id | path
----+--------------------------------------------------------------------
0 | /c[@cd!=@e!="a" and @b and @c or @f]/@g
1 | /a[@a or @cd!=@e!="a" and @b=@k=@l or @s]/b/text()
2 | /state[@tld or "cz"="cz" and @tld and @a or @b]
3 | /state[@tld="de" or @area="78866" and @population="1067494"]
4 | /a[(@b!="a" and @k or @l)]/b/text()
5 | /a[(@b!="a" and (@k or @l))]/b/text()
6 | /a[((@cd and @e)!="a" and @k)]/b[(@a>@c<=@g=@h)=i and @k=l]/text()
7 | /a[@a or ((@e)!="a"=@k or @l)]/b/text()
8 | /a[ab/cd/@a!=b=c/a[@a=@b=a[@c and (@b or @c)]]=@a]
9 | /a[@a and @b or (@c=a[@e=@f])]
10 | /a[@a and @b or (@c=a[@e=f])]
11 | /a[@a and @b or @c=a[@e=@f]]
12 | /a[@a and @b or @c=a[@e=f]]
13 | //root[/a and //b or c]
14 | /root/a[position()=position()]
15 | /root/b[count(a)>=count(c)]
16 | /root[contains("",a)]
(17 rows)
select id, path_debug_print(path)
from paths
order by id asc;
id | path_debug_print
----+---------------------------------------------------------
0 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: c +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| subexpr. implicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| @cd +
| != +
| @e +
| != +
| "a" +
| and +
| @b +
| and +
| @c +
| or +
| @f +
| variables: +
| attribute: cd +
| attribute: e +
| attribute: b +
| attribute: c +
| attribute: f +
| attr. test: @g +
|
1 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| @a +
| or +
| subexpr. implicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| @cd +
| != +
| @e +
| != +
| "a" +
| and +
| subexpr. implicit: (val. type: 0) +
| @b +
| = +
| @k +
| = +
| @l +
| or +
| @s +
| variables: +
| attribute: a +
| attribute: cd +
| attribute: e +
| attribute: b +
| attribute: k +
| attribute: l +
| attribute: s +
| node test: b +
| node test: text() +
|
2 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: state +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| @tld +
| or +
| subexpr. implicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| "cz" +
| = +
| "cz" +
| and +
| @tld +
| and +
| @a +
| or +
| @b +
| variables: +
| attribute: tld +
| attribute: tld +
| attribute: a +
| attribute: b +
|
3 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: state +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| subexpr. implicit: (val. type: 0) +
| @tld +
| = +
| "de" +
| or +
| subexpr. implicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| @area +
| = +
| "78866" +
| and +
| subexpr. implicit: (val. type: 0) +
| @population +
| = +
| "1067494" +
| variables: +
| attribute: tld +
| attribute: area +
| attribute: population +
|
4 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| subexpr. explicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| @b +
| != +
| "a" +
| and +
| @k +
| or +
| @l +
| variables: +
| attribute: b +
| attribute: k +
| attribute: l +
| node test: b +
| node test: text() +
|
5 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| subexpr. explicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| @b +
| != +
| "a" +
| and +
| subexpr. explicit: (val. type: 0) +
| @k +
| or +
| @l +
| variables: +
| attribute: b +
| attribute: k +
| attribute: l +
| node test: b +
| node test: text() +
|
6 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 2 +
| +
| +
| +
| +
| relative xpath +
| +
| node test: i +
| +
| +
| +
| +
| relative xpath +
| +
| node test: l +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| subexpr. explicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| subexpr. explicit: (val. type: 0) +
| @cd +
| and +
| @e +
| != +
| "a" +
| and +
| @k +
| variables: +
| attribute: cd +
| attribute: e +
| attribute: k +
| node test: b +
| predicate expr.: (paths / funcs: 2 / 0, val. type: 0)+
| subexpr. implicit: (val. type: 0) +
| subexpr. explicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| @a +
| > +
| @c +
| <= +
| @g +
| = +
| @h +
| = +
| +
| and +
| subexpr. implicit: (val. type: 0) +
| @k +
| = +
| +
| variables: +
| attribute: a +
| attribute: c +
| attribute: g +
| attribute: h +
| path: 0 +
| attribute: k +
| path: 1 +
| node test: text() +
|
7 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| @a +
| or +
| subexpr. explicit: (val. type: 0) +
| subexpr. implicit: (val. type: 0) +
| subexpr. explicit: (val. type: 3) +
| @e +
| != +
| "a" +
| = +
| @k +
| or +
| @l +
| variables: +
| attribute: a +
| attribute: e +
| attribute: k +
| attribute: l +
| node test: b +
| node test: text() +
|
8 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 4 +
| +
| +
| +
| +
| relative xpath +
| +
| node test: ab +
| node test: cd +
| attr. test: @a +
| +
| +
| +
| +
| relative xpath +
| +
| node test: b +
| +
| +
| +
| +
| relative xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| @c +
| and +
| subexpr. explicit: (val. type: 0) +
| @b +
| or +
| @c +
| variables: +
| attribute: c +
| attribute: b +
| attribute: c +
| +
| +
| +
| +
| relative xpath +
| +
| node test: c +
| node test: a +
| predicate expr.: (paths / funcs: 1 / 0, val. type: 0)+
| @a +
| = +
| @b +
| = +
| +
| variables: +
| attribute: a +
| attribute: b +
| path: 2 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 3 / 0, val. type: 0)+
| +
| != +
| +
| = +
| +
| = +
| @a +
| variables: +
| path: 0 +
| path: 1 +
| path: 3 +
| attribute: a +
|
9 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 1 +
| +
| +
| +
| +
| relative xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| @e +
| = +
| @f +
| variables: +
| attribute: e +
| attribute: f +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 1 / 0, val. type: 0)+
| subexpr. implicit: (val. type: 0) +
| @a +
| and +
| @b +
| or +
| subexpr. explicit: (val. type: 0) +
| @c +
| = +
| +
| variables: +
| attribute: a +
| attribute: b +
| attribute: c +
| path: 0 +
|
10 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 2 +
| +
| +
| +
| +
| relative xpath +
| +
| node test: f +
| +
| +
| +
| +
| relative xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 1 / 0, val. type: 0)+
| @e +
| = +
| +
| variables: +
| attribute: e +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 1 / 0, val. type: 0)+
| subexpr. implicit: (val. type: 0) +
| @a +
| and +
| @b +
| or +
| subexpr. explicit: (val. type: 0) +
| @c +
| = +
| +
| variables: +
| attribute: a +
| attribute: b +
| attribute: c +
| path: 1 +
|
11 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 1 +
| +
| +
| +
| +
| relative xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
| @e +
| = +
| @f +
| variables: +
| attribute: e +
| attribute: f +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 1 / 0, val. type: 0)+
| subexpr. implicit: (val. type: 0) +
| @a +
| and +
| @b +
| or +
| subexpr. implicit: (val. type: 0) +
| @c +
| = +
| +
| variables: +
| attribute: a +
| attribute: b +
| attribute: c +
| path: 0 +
|
12 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 2 +
| +
| +
| +
| +
| relative xpath +
| +
| node test: f +
| +
| +
| +
| +
| relative xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 1 / 0, val. type: 0)+
| @e +
| = +
| +
| variables: +
| attribute: e +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| predicate expr.: (paths / funcs: 1 / 0, val. type: 0)+
| subexpr. implicit: (val. type: 0) +
| @a +
| and +
| @b +
| or +
| subexpr. implicit: (val. type: 0) +
| @c +
| = +
| +
| variables: +
| attribute: a +
| attribute: b +
| attribute: c +
| path: 1 +
|
13 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 3 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: a +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: b (desc.) +
| +
| +
| +
| +
| relative xpath +
| +
| node test: c +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: root (desc.) +
| predicate expr.: (paths / funcs: 3 / 0, val. type: 0)+
| subexpr. implicit: (val. type: 0) +
| +
| and +
| +
| or +
| +
| variables: +
| path: 0 +
| path: 1 +
| path: 2 +
|
14 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 0 +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: root +
| node test: a +
| predicate expr.: (paths / funcs: 0 / 2, val. type: 0)+
| position() +
| = +
| position() +
| variables: +
| function: position() +
| function: position() +
|
15 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 2 +
| +
| +
| +
| +
| relative xpath +
| +
| node test: a +
| +
| +
| +
| +
| relative xpath +
| +
| node test: c +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: root +
| node test: b +
| predicate expr.: (paths / funcs: 2 / 2, val. type: 0)+
| count( +
| +
| ) +
| >= +
| count( +
| +
| ) +
| variables: +
| path: 0 +
| path: 1 +
|
16 | main expr.: (paths / funcs: 1 / 0, val. type: 3) +
| +
| variables: +
| path: 1 +
| +
| +
| +
| +
| relative xpath +
| +
| node test: a +
| +
| +
| +
| +
| absolute xpath +
| +
| node test: root +
| predicate expr.: (paths / funcs: 1 / 1, val. type: 0)+
| contains( +
| "" +
| , +
| +
| ) +
| variables: +
| path: 0 +
|
(17 rows)
-- accidental match of attribute name and element name (bug found during development)
select xml.path('/root/@b', '3');
path
------
(1 row)
select xml.path('/root/@b', '3');
path
------
1
(1 row)
-- some non-trivial XPath predicates
select path('/state[@tld="at" or @area="78866"]/@name', data) from states;
path
-----------------
Österreich
Česká republika
(3 rows)
select path('/state[@tld="de" or @area="78866" and @population="0"]/@tld', data) from states;
path
------
de
(3 rows)
select path('/state[@tld="de" or @area="78866" and @population="10674947"]/@tld', data) from states;
path
------
de
cz
(3 rows)
select path('/doc/a[(@b="1" or @b="2") and @c="3"]', '');
path
------------------
(1 row)
-- MB characters in XPath expression
with paths(id, path) as (
values
((0), ('/dům/střecha'::xml.path)),
((1), ('/dům/poschodí[@číslo="2"]'::xml.path)),
((2), ('/dům/poschodí[@cislo="1"]'::xml.path)),
((3), ('/dům/přízemí'::xml.path))
)
select id, path(path, '')
from paths
order by id;
id | path
----+-----------------------
0 |
1 |
2 |
3 |
(4 rows)
-- subpaths
insert into docs_other(id, data)
values (1, 'xyxy'),
(2, 'xxyyx'),
(3, 'xy1z1z2y2xy');
select id, path('/root[a!=@b]', data)
from docs_other
order by id;
id | path
----+--------------------------------------------------
1 | xyxy
2 |
3 |
(3 rows)
select id, path('/root[a=@b]', data)
from docs_other
order by id;
id | path
----+---------------------------------------------------
1 |
2 | xxyyx
3 |
(3 rows)
select id, path('/root[b="xy"]', data)
from docs_other
order by id;
id | path
----+---------------------------------------------------------------------------
1 | xyxy
2 |
3 | xy1z1z2y2xy
(3 rows)
-- check that descendant text nodes are concantenated and compared correctly
select id, path('/root[a="xy1z1z2y2"]', data)
from docs_other
order by id;
id | path
----+---------------------------------------------------------------------------
1 |
2 |
3 | xy1z1z2y2xy
(3 rows)
select id, path('/root[a="xy1z1z2y"]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
select id, path('/root[a="xy1z1z2y23"]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
-- compare non-empty node set to zero-length literal
select id, path('/root[a=""]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
select id, path('/root[a!=""]', data)
from docs_other
order by id;
id | path
----+---------------------------------------------------------------------------
1 | xyxy
2 | xxyyx
3 | xy1z1z2y2xy
(3 rows)
-- compare non-empty node set, having no text nodes, to zero-length literal
select id, path('/root[b=""]', data)
from docs_other
order by id;
id | path
----+---------------------------------------------------------------------------
1 |
2 |
3 | xy1z1z2y2xy
(3 rows)
select id, path('/root[b!=""]', data)
from docs_other
order by id;
id | path
----+---------------------------------------------------------------------------
1 | xyxy
2 | xxyyx
3 | xy1z1z2y2xy
(3 rows)
-- compare empty node set to zero-length literal
select id, path('/root[f=""]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
select id, path('/root[f!=""]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
-- compare non-empty node, having no text nodes, set to non-zero-length literal
select id, path('/root[b="xy1z1z2y2"]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
select id, path('/root[b!="xy1z1z2y2"]', data)
from docs_other
order by id;
id | path
----+---------------------------------------------------------------------------
1 | xyxy
2 | xxyyx
3 | xy1z1z2y2xy
(3 rows)
-- compare empty node set to non-zero-length literal
select id, path('/root[f="xy1z1z2y2"]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
select id, path('/root[f!="xy1z1z2y2"]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
-- compare non-empty node set to null value (attribute that doesn't exist)
select id, path('/root[a=@t]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
select id, path('/root[a!=@t]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
-- compare non-empty node set, having no text nodes, to null value
select id, path('/root[b=@t]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
select id, path('/root[b!=@t]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
-- compare empty node set to null value
select id, path('/root[f=@t]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
select id, path('/root[f!=@t]', data)
from docs_other
order by id;
id | path
----+------
1 |
2 |
3 |
(3 rows)
-- compare string to a set of text nodes (only sets of elements tested so far)
select path('/root[@i=a/text()]', 'y');
path
------
(1 row)
select path('/root[@i!=a/text()]', 'x');
path
------
(1 row)
select path('/root[@i=a/text()]', 'xy');
path
-------------------------------------
xy
(1 row)
select path('/root[@i!=a/text()]', 'xy');
path
-------------------------------------
xy
(1 row)
-- compare 2 node sets
select path('/root[a=b]', '');
path
-----------------------------------
(1 row)
select path('/root[a=b]', 'x1x2x1x2');
path
---------------------------------------------------------
x1x2x1x2
(1 row)
select path('/root[a=b]', 'x1y1z1y3x2x1y1z1y3x2');
path
-------------------------------------------------------------------------------
x1y1z1y3x2x1y1z1y3x2
(1 row)
select path('/root[a=b]', 'x1y1z1y3x2x3x1y1z1y3x2');
path
------
(1 row)
select path('/root[a=b]', 'x1x2x2');
path
------------------------------------------------------------
x1x2x2
(1 row)
select path('/root[a!=b]', 'x1x2x2');
path
------------------------------------------------------------
x1x2x2
(1 row)
-- non-existing subpaths
select path('/root[a=a/b]', 'y');
path
------
(1 row)
select path('/root[a!=a/b]', 'y');
path
------
(1 row)
-- again, test sets of other than element nodes
select path('/root[a=a/@a]', 'y');
path
-----------------------------------
y
(1 row)
select path('/root[a!=a/@a]', 'y');
path
------
(1 row)
select path('/root[a!=a/@*]', 'x');
path
-----------------------------------
x
(1 row)
select path('/root[a/@*=a]', 'x');
path
-----------------------------------
x
(1 row)
select path('/root[a=comment()]', 'x');
path
-------------------------------
x
(1 row)
select path('/root[a!=comment()]', 'x');
path
------
(1 row)
select path('/root[a=b/node()]', 'x');
path
------
(1 row)
select path('/root[a!=b/node()]', 'x');
path
-------------------------------
xy
(1 row)
select path('/root[a=b/processing-instruction("abc")]', 'def');
path
-------------------------------------------
def
(1 row)
select path('/root[a!=b/processing-instruction("abc")]', 'def');
path
------
(1 row)
-- sets where non-element is on both sides
select path('/root[node()=node()]', '');
path
----------------
x
(1 row)
select path('/root[node()!=node()]', '');
path
------
(1 row)
-- attributes having numeric values
select path('/root[@b=1.5 and @c=1.1000000]', '');
path
--------------------------
(1 row)
select path('/root[@b!=1.5]', '');
path
------
(1 row)
-- Compare number to a node-set. First, set of elements...
select path('/root[b=0.1]', ' 0.1 ');
path
-----------------------------------
0.1
(1 row)
select path('/root[b!=0.1]', ' 0.1 ');
path
------
(1 row)
-- ... and then also that of another type:
select path('/root[comment()=1.551]', '');
path
------------------------------
(1 row)
select path('/root[comment()=1.551]', '');
path
------
(1 row)
select path('/root[comment()!=1.551]', '');
path
----------------------
(1 row)
-- Operators '<' and '>'
select path('/root[@b>1.01]', '');
path
-----------------
(1 row)
select path('/root[@b<1.01]', '');
path
------
(1 row)
select path('/root[1.01<@b]', '');
path
-----------------
(1 row)
select path('/root[1.01>@b]', '');
path
------
(1 row)
select path('/root[b<1]', '1.5');
path
------
(1 row)
select path('/root[b>1]', '1.5');
path
-------------------------
1.5
(1 row)
select path('/root[11.5');
path
-------------------------
1.5
(1 row)
select path('/root[1>b]', '1.5');
path
------
(1 row)
select path('/a/@j=1', '');
path
-------
false
(1 row)
select path('/a/@j<1', '');
path
-------
false
(1 row)
select path('/a/@j!=1', '');
path
------
true
(1 row)
select xml.path('/=/a', '');
path
------
true
(1 row)
select xml.path('/=/b', '');
path
-------
false
(1 row)
select xml.path('/=/', '');
path
------
true
(1 row)
select xml.path('/=/b', '');
path
-------
false
(1 row)
select xml.path('/!=/b', '');
path
-------
false
(1 row)
-- No match if one operand can't be cast to a number:
select path('/root[@b>1.1]', '');
path
------
(1 row)
select path('/root[@b<1.1]', '');
path
------
(1 row)
select path('/root[@b<"a"]', '');
path
------
(1 row)
select path('/root[@b>"a"]', '');
path
------
(1 row)
select path('/root[@b>b]', 'def');
path
------
(1 row)
select path('/root[@bdef');
path
------
(1 row)
-- Operator '|' (union)
-- First, check that operator output type is correctly set:
select xml.path_debug_print('/a[@i > @j]|/b or /c');
path_debug_print
---------------------------------------------------------
main expr.: (paths / funcs: 3 / 0, val. type: 0) +
subexpr. implicit: (val. type: 3) +
+
| +
+
or +
+
variables: +
path: 0 +
path: 1 +
path: 2 +
+
+
+
+
absolute xpath +
+
node test: a +
predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
@i +
> +
@j +
variables: +
attribute: i +
attribute: j +
+
+
+
+
absolute xpath +
+
node test: b +
+
+
+
+
absolute xpath +
+
node test: c +
(1 row)
select xml.path_debug_print('(/a[@i > @j]|/b) or /c');
path_debug_print
---------------------------------------------------------
main expr.: (paths / funcs: 3 / 0, val. type: 0) +
subexpr. explicit: (val. type: 3) +
+
| +
+
or +
+
variables: +
path: 0 +
path: 1 +
path: 2 +
+
+
+
+
absolute xpath +
+
node test: a +
predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
@i +
> +
@j +
variables: +
attribute: i +
attribute: j +
+
+
+
+
absolute xpath +
+
node test: b +
+
+
+
+
absolute xpath +
+
node test: c +
(1 row)
select xml.path_debug_print('((/a[@i > @j]|/b) or /c)');
path_debug_print
---------------------------------------------------------
main expr.: (paths / funcs: 3 / 0, val. type: 0) +
subexpr. explicit: (val. type: 0) +
subexpr. explicit: (val. type: 3) +
+
| +
+
or +
+
variables: +
path: 0 +
path: 1 +
path: 2 +
+
+
+
+
absolute xpath +
+
node test: a +
predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
@i +
> +
@j +
variables: +
attribute: i +
attribute: j +
+
+
+
+
absolute xpath +
+
node test: b +
+
+
+
+
absolute xpath +
+
node test: c +
(1 row)
-- Does the union operator eliminate duplicate nodes?
select xml.path('/root/a|/root/a', '');
path
------
(1 row)
select xml.path('/root/a|/root/a|/root', '');
path
-----------------------
(1 row)
-- Operators '+' and '-'
-- Again, check result type
select xml.path_debug_print('/root[@i + @j > 0]');
path_debug_print
---------------------------------------------------------
main expr.: (paths / funcs: 1 / 0, val. type: 3) +
+
variables: +
path: 0 +
+
+
+
+
absolute xpath +
+
node test: root +
predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
subexpr. implicit: (val. type: 1) +
@i +
+ +
@j +
> +
0.00 +
variables: +
attribute: i +
attribute: j +
(1 row)
select xml.path_debug_print('/root[(@i + @j) > 0]');
path_debug_print
---------------------------------------------------------
main expr.: (paths / funcs: 1 / 0, val. type: 3) +
+
variables: +
path: 0 +
+
+
+
+
absolute xpath +
+
node test: root +
predicate expr.: (paths / funcs: 0 / 0, val. type: 0)+
subexpr. explicit: (val. type: 1) +
@i +
+ +
@j +
> +
0.00 +
variables: +
attribute: i +
attribute: j +
(1 row)
-- Simple examples
select xml.path('/root/@i + /root/@j', '');
path
------
3
(1 row)
select xml.path('/root/@i - /root/@j', '');
path
------
-1
(1 row)
select xml.path('boolean(/root/@i + /root/@j)', '');
path
------
true
(1 row)
select xml.path('string(/root/@i + /root/@j)', '');
path
------
3
(1 row)
select xml.path('/root[/root/@i + /root/@j]', '');
path
------
(1 row)
select xml.path('/root[(/root/@j - /root/@i)]', '');
path
---------------------
(1 row)
select xml.path('/root[/root/@j - /root/@i]', '');
path
---------------------
(1 row)
-- Involve null values
select xml.path('/root/@i + /root/@k', '');
path
------
(1 row)
select xml.path('/root/@i - /root/@k', '');
path
------
(1 row)
select xml.path('(/root/@j + /root/@k) = (/root/@j + /root/@k)', '');
path
-------
false
(1 row)
select xml.path('(/root/@j + /root/@k) != (/root/@j + /root/@k)', '');
path
------
true
(1 row)
select xml.path('(/root/@j + /root/@k) = 2', '');
path
-------
false
(1 row)
select xml.path('(/root/@j + /root/@k) != 2', '');
path
------
true
(1 row)
select xml.path('/root/@j + /root/@k', '');
path
------
(1 row)
select xml.path('(/root/@j + /root/@k)', '');
path
------
(1 row)
select xml.path('boolean(/root/@j + /root/@k)', '');
path
-------
false
(1 row)
select xml.path('string(/root/@j + /root/@k)', '');
path
------
(1 row)
select xml.path('/root[/root/@j + /root/@k]', '');
path
------
(1 row)
select xml.path('/root[(/root/@j + /root/@k)]', '');
path
------
(1 row)
select xml.path('boolean(1+/root)', '');
path
-------
false
(1 row)
select xml.path('string(1+/root)', '');
path
------
(1 row)
select xml.path('boolean(1+/root)', '1');
path
------
true
(1 row)
select xml.path('string(1+/root)', '1');
path
------
2
(1 row)
-- null values where short evaluation is expected
select xml.path('boolean(false() or /root/@j + /root/@k or false())', '');
path
-------
false
(1 row)
select xml.path('boolean(true() and /root/@j + /root/@k and true())', '');
path
-------
false
(1 row)
-- Multiply operator
select xml.path_debug_print('/a/@i*/a/@j');
path_debug_print
--------------------------------------------------
main expr.: (paths / funcs: 2 / 0, val. type: 1)+
+
* +
+
variables: +
path: 0 +
path: 1 +
+
+
+
+
absolute xpath +
+
node test: a +
attr. test: @i +
+
+
+
+
absolute xpath +
+
node test: a +
attr. test: @j +
(1 row)
select xml.path_debug_print('/a/@**/a/@j');
path_debug_print
--------------------------------------------------
main expr.: (paths / funcs: 2 / 0, val. type: 1)+
+
* +
+
variables: +
path: 0 +
path: 1 +
+
+
+
+
absolute xpath +
+
node test: a +
attr. test: @* +
+
+
+
+
absolute xpath +
+
node test: a +
attr. test: @j +
(1 row)
select xml.path('/a/@i*/a/@j', '');
path
------
6
(1 row)
select xml.path('/a/@**/a/@j', '');
path
------
6
(1 row)
-- Unary operator
select xml.path('boolean(-/root)', '');
path
-------
false
(1 row)
select xml.path('boolean((-/root))', '');
path
-------
false
(1 row)
select xml.path('string(-/root)', '');
path
------
(1 row)
select xml.path('string((-/root))', '');
path
------
(1 row)
select xml.path('-/root<3', '3');
path
------
true
(1 row)
select xml.path('-/root+-2', '3');
path
------
-5
(1 row)
select xml.path('/root + - boolean(/root)', '3');
path
------
2
(1 row)
select xml.path('/root/@i + - 1 + 2', '');
path
------
4
(1 row)
select xml.path('/root/@i + - (1 + 2)', '');
path
------
0
(1 row)
select xml.path_debug_print('/root + - boolean(/root)');
path_debug_print
--------------------------------------------------
main expr.: (paths / funcs: 2 / 1, val. type: 1)+
+
+ +
- boolean( +
+
) +
variables: +
path: 0 +
path: 1 +
+
+
+
+
absolute xpath +
+
node test: root +
+
+
+
+
absolute xpath +
+
node test: root +
(1 row)
select xml.path_debug_print('/root/@i + - 1 + 2');
path_debug_print
--------------------------------------------------
main expr.: (paths / funcs: 1 / 0, val. type: 1)+
+
+ +
-1.00 +
+ +
2.00 +
variables: +
path: 0 +
+
+
+
+
absolute xpath +
+
node test: root +
attr. test: @i +
(1 row)
select xml.path_debug_print('/root/@i + - (1 + 2)');
path_debug_print
--------------------------------------------------
main expr.: (paths / funcs: 1 / 0, val. type: 1)+
+
+ +
- subexpr. explicit: (val. type: 1) +
1.00 +
+ +
2.00 +
variables: +
path: 0 +
+
+
+
+
absolute xpath +
+
node test: root +
attr. test: @i +
(1 row)
--Special cases where the '-' has to be propagated to top-level exression
select xml.path('-(-(-(1)))', '');
path
------
-1
(1 row)
select xml.path('-(-(-1))', '');
path
------
-1
(1 row)
select xml.path('-(-(1))', '');
path
------
1
(1 row)
select xml.path('-(-1)', '');
path
------
1
(1 row)
select xml.path('-(1)', '');
path
------
-1
(1 row)
select xml.path('-1', '');
path
------
-1
(1 row)
select xml.path('-(-(1 - 2))', '');
path
------
-1
(1 row)
--..similar, with functions instead of subexpressions
select xml.path('-((-(1 - 2) + -count(/a)))', '');
path
------
-0
(1 row)
select xml.path('-((-(1 - 2) + -count(/a)))', '');
path
------
-0
(1 row)
select xml.path('-((-(1 - 2) + -count(/a/b)))', '');
path
------
1
(1 row)
select xml.path('-((-(1 - 2) + (-count(/a/b))))', '');
path
------
1
(1 row)
select xml.path('-((-(1 - 2) + -(-count(/a/b))))', '');
path
------
-3
(1 row)
select xml.path('-((-(1 - 2) + -(count(/a/b))))', '');
path
------
1
(1 row)
--...and some where node test predicate is the top-level expression
select xml.path('/root/a[-((-1 + -count(/root)))]', '');
path
------
(1 row)
select xml.path('/root/a[-((-(1) + -count(/root/a)))]', '');
path
------
(1 row)
select xml.path('/root/a[-((-1 + -count(/root/a)))]', '');
path
------
(1 row)
select xml.path('/root/a[-((-(1) + (-count(/root/a))))]', '');
path
------
(1 row)
select xml.path('/root/a[-((-1 + -(-count(/root/a))))]', '');
path
------
(1 row)
select xml.path('/root/a[-((-(1) + -(count(/root/a))))]', '');
path
------
(1 row)
-- The XPath predicate can be used even if the element has no children/attributes
select path('/root["a"]', '');
path
---------
(1 row)
select path('/root[@a or a]', '');
path
------
(1 row)
-- Absolute sub-path
select path('/root[/root/a=x]', 'cc');
path
-------------------------------
cc
(1 row)
select path('/root[/root/a=/x]', 'cc');
path
------
(1 row)
select path('/root[//a=x]', 'cc');
path
-------------------------------
cc
(1 row)
-- Special case: the sub-path represents the whole document:
select path('/root[/=/]', '');
path
---------
(1 row)
select path('/root[/!=/]', '');
path
------
(1 row)
select path('/root[/=1]', '');
path
------
(1 row)
select path('/root[/>1]', '');
path
------
(1 row)
select path('/root[/=a]', '');
path
-------------------
(1 row)
select path('/root[/!=a]', '');
path
------
(1 row)
-- position() function (implicit)
select path('/root/a[position()=@i]', '');
path
--------------
(1 row)
select path('/root/a[position()=1 or position()=2]', '');
path
----------------------
(1 row)
select path('/state/city[6]/@name', data) from states where id=1;
path
------------------
České Budějovice
(1 row)
select path('/state/region[5]/city[1]/@name', data) from states where id=2;
path
-------------
Saarbrücken
(1 row)
select path('/root//a[position()=3]', 'xy');
path
------------
(1 row)
select id, path('/state/comment()', data)
from states
order by id;
id | path
----+----------------------------------------------------------------
1 |
2 |
3 |
(3 rows)
-- node() test: verify that only non-attribute child nodes are returned
select path('/a/node()', '');
path
-----------------------------------------------
(1 row)
-- all attributes but no other nodes
select path('/a/@*', '');
path
------
1
(1 row)
-- Some more exercises for '@*'
select xml.path('/root[count(@*)]', '');
path
------
(1 row)
select xml.path('/root[count(@*)]', '');
path
---------------
(1 row)
select xml.path('/root[@*=@*]', '');
path
---------------------
(1 row)
select xml.path('/root[@*!=@*]', '');
path
---------------------
(1 row)
select xml.path('/root[@*=@*]', '');
path
---------------
(1 row)
select xml.path('/root[@*!=@*]', '');
path
------
(1 row)
select xml.path('/root[@*=@*]', '');
path
------
(1 row)
select xml.path('/root[@*!=@*]', '');
path
------
(1 row)
select xml.path('/@i', '');
path
------
(1 row)
select xml.path('/@*', '');
path
------
(1 row)
select xml.path('/root', '{"@*"}', '');
path
------
{1}
(1 row)
select xml.path('//@id', '');
path
------
1
(1 row)
-- Related case: if the attribute operand is wrapped in (sub)expression, that expression's value type must be node set
select xml.path_debug_print('count((@*))');
path_debug_print
--------------------------------------------------
main expr.: (paths / funcs: 0 / 1, val. type: 1)+
count( +
subexpr. explicit: (val. type: 3) +
@* +
) +
variables: +
attribute: *
(1 row)
-- descendants
select path('/a//b', '');
path
--------------------------
(1 row)
select path('//x/b', '');
path
------------------------------------------------------
(1 row)
-- special case: with 2 location steps searching for descendants, some nodes may be found multiple times (by various combinations
-- of scan and its sub-scan). In this case that would happen for '' if uniqueness wasn't enforced:
select path('//x//b', '');
path
---------------------------------------------------------------------
(1 row)
-- Functions
-- string() as such tested above. In this special case it's applied on document.
select path('string(/)', 'x');
path
------
x
(1 row)
select path('/root/b[count(a)>1]', '');
path
------
(1 row)
select path('/root/b[count(c)=0]', '');
path
------
(1 row)
select path('/root[contains(a, "x")]', 'xyz');
path
--------------------------------
xyz
(1 row)
select path('/root[contains(a, "z")]', 'xyz');
path
------
(1 row)
select path('/root[contains(a, "")]', 'xyz');
path
--------------------------------
xyz
(1 row)
select path('/root[contains("", a)]', 'xyz');
path
------
(1 row)
select path('/root[contains("", a)]', 'xyz');
path
------
(1 row)
select path('/root[contains(b, a)]', 'xyz');
path
------
(1 row)
select path('/root[contains(a, b)]', 'xyz');
path
--------------------------------
xyz
(1 row)
select path('/root[contains(b, c)]', 'xyz');
path
--------------------------------
xyz
(1 row)
select path('contains("c", /root/a)', '');
path
------
true
(1 row)
select path('/state[contains(@name, "rep") and (@area<=100000 or @population>11000000)]', '{@name, @area, @population}', data)
from states
order by id;
path
------------------------------------------------
{"Česká republika",78866,10674947}
{"Bundesrepublik Deutschland",357021,81757600}
(2 rows)
select id, path('count(/state/region)', data)
from states
order by id;
id | path
----+------
1 | 1
2 | 5
3 | 3
(3 rows)
select path('sum(/)', '1231 ');
path
------
1231
(1 row)
select path('sum(/a)', '1231 ');
path
------
1231
(1 row)
select path('sum(/a/b)', '1231 ');
path
------
33
(1 row)
select path('sum(/a/b)', '123 1 ');
path
------
(1 row)
select path('sum(//@*)', '1');
path
------
4
(1 row)
-- Special case: the node-set is empty
select path('sum(/a/x)', '1');
path
------
0
(1 row)
select path('/root//a[position()=last()]', '');
path
----------------
(1 row)
select path('/root//a[position()=last() and @i]', '');
path
------------
(1 row)
select xml.path('/root[concat("a", /root, "c", /root)="axcx"]', 'x');
path
----------------
x
(1 row)
select xml.path('/root[concat("a", /root, "c")="axcx"]', 'x');
path
------
(1 row)
select xml.path('name(/)', '');
path
------
(1 row)
select xml.path('name(/a/b)', '');
path
------
b
(1 row)
select xml.path('name(/a/@i)', '');
path
------
i
(1 row)
select xml.path('name(/a/node())', '');
path
------
b
(1 row)
select xml.path('/a[name()="a"]', '');
path
------
(1 row)
select xml.path('/a[name()!="a"]', '');
path
------
(1 row)
select xml.path('/a[name()="b"]', '');
path
------
(1 row)
select xml.path('/a[name(/)="b"]', '');
path
------
(1 row)
select xml.path('/a[name(/)!="b"]', '');
path
------
(1 row)
select xml.path('/a[name()!="b"]', '');
path
------
(1 row)
select xml.path('/a[name()=""]', '');
path
------
(1 row)
select xml.path('/a[name()!=""]', '');
path
------
(1 row)
select path('local-name(/a/node())', '');
path
------
b
(1 row)
select path('/a/b[local-name()="b"]', '');
path
------
(1 row)
select path('/a/b[local-name()!="b"]', '');
path
------
(1 row)
--Special case: name() used relative to '/' base path
select path('/', '{"name()"}', '');
path
------
(0 rows)
-- Nested functions. In addition, result has to be implicitly cast.
select xml.path('concat(count(/root/a), /root)', '50');
path
------
050
(1 row)
select xml.path('concat(count(/root), /root)', '50');
path
------
150
(1 row)
-- DOM
select xml.children('');
children
------------------------------------------------
{,"",}
(1 row)
select xml.element('root', '{{"i", "x"}, {"j", 1}, {"k", ""}}', '');
element
----------------------------------------
(1 row)
select xml.element('root', '{{"i", "x"}, {"j", 1}}', '');
element
--------------------------------------------------
(1 row)
select xml.element('root', NULL, '');
element
--------------------------------------
(1 row)
select xml.element('root', '{{"i", "x"}, {"j", 1}}', NULL);
element
---------------------
(1 row)
-- xml.fragment()
WITH tmp(n) as (VALUES (''), (''), (''))
SELECT xml.fragment(n::xml.node)
FROM tmp;
fragment
-------------------------------------
(1 row)
-- Cleanup
drop table states;
drop table paths;
drop table docs_other;