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;
-- 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;
select id, path('count(//city)', data)
from states
order by id;
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;
-- 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;
select s.id, path(p.path, data)
from states s
join paths p on (p.name='regions')
order by s.id;
-- Convert data to table-like form
select id, path('/state', '{"region/city", "@area", "@population", "@tld"}', data) as state_info
from states
order by id;
-- 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;
-- 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;
-- remove() - repeated occurrence of the target path:
update states set data=remove(data, '/state/text()');
select data
from states
order by id;
-- Additional tests
-- add() - repeated occurrence of the target path:
select add('', '/a/b', '', 'b');
select add('', '/a/b', '', 'a');
select add('', '/a/b', '', 'r');
-- insert into both empty and non-empty element
select add('', '/a/b', '', 'i');
select add('', '/a/b', '', 'i');
select add('', '/a/b', '', 'i');
-- and the same for document fragment:
select add('', '/a/b', '', 'b');
select add('', '/a/b', '', 'a');
select add('', '/a/b', '', 'r');
select add('', '/a/b', '', 'i');
select add('', '/a/b', '', 'i');
select add('', '/a/b', '', 'i');
-- remove() - repeated occurrence of the target path:
-- remove attribute
select remove('', '/a/b/@id');
-- remove the last attribute
select remove('', '/a/b/@id');
-- remove the last nested node(s)
select remove('', '/a/b');
-- 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;
select id, path_debug_print(path)
from paths
order by id asc;
-- accidental match of attribute name and element name (bug found during development)
select xml.path('/root/@b', '3');
select xml.path('/root/@b', '3');
-- some non-trivial XPath predicates
select path('/state[@tld="at" or @area="78866"]/@name', data) from states;
select path('/state[@tld="de" or @area="78866" and @population="0"]/@tld', data) from states;
select path('/state[@tld="de" or @area="78866" and @population="10674947"]/@tld', data) from states;
select path('/doc/a[(@b="1" or @b="2") and @c="3"]', '');
-- 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;
-- 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;
select id, path('/root[a=@b]', data)
from docs_other
order by id;
select id, path('/root[b="xy"]', data)
from docs_other
order by id;
-- check that descendant text nodes are concantenated and compared correctly
select id, path('/root[a="xy1z1z2y2"]', data)
from docs_other
order by id;
select id, path('/root[a="xy1z1z2y"]', data)
from docs_other
order by id;
select id, path('/root[a="xy1z1z2y23"]', data)
from docs_other
order by id;
-- compare non-empty node set to zero-length literal
select id, path('/root[a=""]', data)
from docs_other
order by id;
select id, path('/root[a!=""]', data)
from docs_other
order by id;
-- 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;
select id, path('/root[b!=""]', data)
from docs_other
order by id;
-- compare empty node set to zero-length literal
select id, path('/root[f=""]', data)
from docs_other
order by id;
select id, path('/root[f!=""]', data)
from docs_other
order by id;
-- 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;
select id, path('/root[b!="xy1z1z2y2"]', data)
from docs_other
order by id;
-- compare empty node set to non-zero-length literal
select id, path('/root[f="xy1z1z2y2"]', data)
from docs_other
order by id;
select id, path('/root[f!="xy1z1z2y2"]', data)
from docs_other
order by id;
-- 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;
select id, path('/root[a!=@t]', data)
from docs_other
order by id;
-- 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;
select id, path('/root[b!=@t]', data)
from docs_other
order by id;
-- compare empty node set to null value
select id, path('/root[f=@t]', data)
from docs_other
order by id;
select id, path('/root[f!=@t]', data)
from docs_other
order by id;
-- compare string to a set of text nodes (only sets of elements tested so far)
select path('/root[@i=a/text()]', 'y');
select path('/root[@i!=a/text()]', 'x');
select path('/root[@i=a/text()]', 'xy');
select path('/root[@i!=a/text()]', 'xy');
-- compare 2 node sets
select path('/root[a=b]', '');
select path('/root[a=b]', 'x1x2x1x2');
select path('/root[a=b]', 'x1y1z1y3x2x1y1z1y3x2');
select path('/root[a=b]', 'x1y1z1y3x2x3x1y1z1y3x2');
select path('/root[a=b]', 'x1x2x2');
select path('/root[a!=b]', 'x1x2x2');
-- non-existing subpaths
select path('/root[a=a/b]', 'y');
select path('/root[a!=a/b]', 'y');
-- again, test sets of other than element nodes
select path('/root[a=a/@a]', 'y');
select path('/root[a!=a/@a]', 'y');
select path('/root[a!=a/@*]', 'x');
select path('/root[a/@*=a]', 'x');
select path('/root[a=comment()]', 'x');
select path('/root[a!=comment()]', 'x');
select path('/root[a=b/node()]', 'x');
select path('/root[a!=b/node()]', 'x');
select path('/root[a=b/processing-instruction("abc")]', 'def');
select path('/root[a!=b/processing-instruction("abc")]', 'def');
-- sets where non-element is on both sides
select path('/root[node()=node()]', '');
select path('/root[node()!=node()]', '');
-- attributes having numeric values
select path('/root[@b=1.5 and @c=1.1000000]', '');
select path('/root[@b!=1.5]', '');
-- Compare number to a node-set. First, set of elements...
select path('/root[b=0.1]', ' 0.1 ');
select path('/root[b!=0.1]', ' 0.1 ');
-- ... and then also that of another type:
select path('/root[comment()=1.551]', '');
select path('/root[comment()=1.551]', '');
select path('/root[comment()!=1.551]', '');
-- Operators '<' and '>'
select path('/root[@b>1.01]', '');
select path('/root[@b<1.01]', '');
select path('/root[1.01<@b]', '');
select path('/root[1.01>@b]', '');
select path('/root[b<1]', '1.5');
select path('/root[b>1]', '1.5');
select path('/root[11.5');
select path('/root[1>b]', '1.5');
select path('/a/@j=1', '');
select path('/a/@j<1', '');
select path('/a/@j!=1', '');
select xml.path('/=/a', '');
select xml.path('/=/b', '');
select xml.path('/=/', '');
select xml.path('/=/b', '');
select xml.path('/!=/b', '');
-- No match if one operand can't be cast to a number:
select path('/root[@b>1.1]', '');
select path('/root[@b<1.1]', '');
select path('/root[@b<"a"]', '');
select path('/root[@b>"a"]', '');
select path('/root[@b>b]', 'def');
select path('/root[@bdef');
-- Operator '|' (union)
-- First, check that operator output type is correctly set:
select xml.path_debug_print('/a[@i > @j]|/b or /c');
select xml.path_debug_print('(/a[@i > @j]|/b) or /c');
select xml.path_debug_print('((/a[@i > @j]|/b) or /c)');
-- Does the union operator eliminate duplicate nodes?
select xml.path('/root/a|/root/a', '');
select xml.path('/root/a|/root/a|/root', '');
-- Operators '+' and '-'
-- Again, check result type
select xml.path_debug_print('/root[@i + @j > 0]');
select xml.path_debug_print('/root[(@i + @j) > 0]');
-- Simple examples
select xml.path('/root/@i + /root/@j', '');
select xml.path('/root/@i - /root/@j', '');
select xml.path('boolean(/root/@i + /root/@j)', '');
select xml.path('string(/root/@i + /root/@j)', '');
select xml.path('/root[/root/@i + /root/@j]', '');
select xml.path('/root[(/root/@j - /root/@i)]', '');
select xml.path('/root[/root/@j - /root/@i]', '');
-- Involve null values
select xml.path('/root/@i + /root/@k', '');
select xml.path('/root/@i - /root/@k', '');
select xml.path('(/root/@j + /root/@k) = (/root/@j + /root/@k)', '');
select xml.path('(/root/@j + /root/@k) != (/root/@j + /root/@k)', '');
select xml.path('(/root/@j + /root/@k) = 2', '');
select xml.path('(/root/@j + /root/@k) != 2', '');
select xml.path('/root/@j + /root/@k', '');
select xml.path('(/root/@j + /root/@k)', '');
select xml.path('boolean(/root/@j + /root/@k)', '');
select xml.path('string(/root/@j + /root/@k)', '');
select xml.path('/root[/root/@j + /root/@k]', '');
select xml.path('/root[(/root/@j + /root/@k)]', '');
select xml.path('boolean(1+/root)', '');
select xml.path('string(1+/root)', '');
select xml.path('boolean(1+/root)', '1');
select xml.path('string(1+/root)', '1');
-- null values where short evaluation is expected
select xml.path('boolean(false() or /root/@j + /root/@k or false())', '');
select xml.path('boolean(true() and /root/@j + /root/@k and true())', '');
-- Multiply operator
select xml.path_debug_print('/a/@i*/a/@j');
select xml.path_debug_print('/a/@**/a/@j');
select xml.path('/a/@i*/a/@j', '');
select xml.path('/a/@**/a/@j', '');
-- Unary operator
select xml.path('boolean(-/root)', '');
select xml.path('boolean((-/root))', '');
select xml.path('string(-/root)', '');
select xml.path('string((-/root))', '');
select xml.path('-/root<3', '3');
select xml.path('-/root+-2', '3');
select xml.path('/root + - boolean(/root)', '3');
select xml.path('/root/@i + - 1 + 2', '');
select xml.path('/root/@i + - (1 + 2)', '');
select xml.path_debug_print('/root + - boolean(/root)');
select xml.path_debug_print('/root/@i + - 1 + 2');
select xml.path_debug_print('/root/@i + - (1 + 2)');
--Special cases where the '-' has to be propagated to top-level exression
select xml.path('-(-(-(1)))', '');
select xml.path('-(-(-1))', '');
select xml.path('-(-(1))', '');
select xml.path('-(-1)', '');
select xml.path('-(1)', '');
select xml.path('-1', '');
select xml.path('-(-(1 - 2))', '');
--..similar, with functions instead of subexpressions
select xml.path('-((-(1 - 2) + -count(/a)))', '');
select xml.path('-((-(1 - 2) + -count(/a)))', '');
select xml.path('-((-(1 - 2) + -count(/a/b)))', '');
select xml.path('-((-(1 - 2) + (-count(/a/b))))', '');
select xml.path('-((-(1 - 2) + -(-count(/a/b))))', '');
select xml.path('-((-(1 - 2) + -(count(/a/b))))', '');
--...and some where node test predicate is the top-level expression
select xml.path('/root/a[-((-1 + -count(/root)))]', '');
select xml.path('/root/a[-((-(1) + -count(/root/a)))]', '');
select xml.path('/root/a[-((-1 + -count(/root/a)))]', '');
select xml.path('/root/a[-((-(1) + (-count(/root/a))))]', '');
select xml.path('/root/a[-((-1 + -(-count(/root/a))))]', '');
select xml.path('/root/a[-((-(1) + -(count(/root/a))))]', '');
-- The XPath predicate can be used even if the element has no children/attributes
select path('/root["a"]', '');
select path('/root[@a or a]', '');
-- Absolute sub-path
select path('/root[/root/a=x]', 'cc');
select path('/root[/root/a=/x]', 'cc');
select path('/root[//a=x]', 'cc');
-- Special case: the sub-path represents the whole document:
select path('/root[/=/]', '');
select path('/root[/!=/]', '');
select path('/root[/=1]', '');
select path('/root[/>1]', '');
select path('/root[/=a]', '');
select path('/root[/!=a]', '');
-- position() function (implicit)
select path('/root/a[position()=@i]', '');
select path('/root/a[position()=1 or position()=2]', '');
select path('/state/city[6]/@name', data) from states where id=1;
select path('/state/region[5]/city[1]/@name', data) from states where id=2;
select path('/root//a[position()=3]', 'xy');
select id, path('/state/comment()', data)
from states
order by id;
-- node() test: verify that only non-attribute child nodes are returned
select path('/a/node()', '');
-- all attributes but no other nodes
select path('/a/@*', '');
-- Some more exercises for '@*'
select xml.path('/root[count(@*)]', '');
select xml.path('/root[count(@*)]', '');
select xml.path('/root[@*=@*]', '');
select xml.path('/root[@*!=@*]', '');
select xml.path('/root[@*=@*]', '');
select xml.path('/root[@*!=@*]', '');
select xml.path('/root[@*=@*]', '');
select xml.path('/root[@*!=@*]', '');
select xml.path('/@i', '');
select xml.path('/@*', '');
select xml.path('/root', '{"@*"}', '');
select xml.path('//@id', '');
-- 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((@*))');
-- descendants
select path('/a//b', '');
select path('//x/b', '');
-- 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', '');
-- Functions
select path('/root/b[count(a)>1]', '');
select path('/root/b[count(c)=0]', '');
select path('/root[contains(a, "x")]', 'xyz');
select path('/root[contains(a, "z")]', 'xyz');
select path('/root[contains(a, "")]', 'xyz');
select path('/root[contains("", a)]', 'xyz');
select path('/root[contains("", a)]', 'xyz');
select path('/root[contains(b, a)]', 'xyz');
select path('/root[contains(a, b)]', 'xyz');
select path('/root[contains(b, c)]', 'xyz');
select path('contains("c", /root/a)', '');
select path('/state[contains(@name, "rep") and (@area<=100000 or @population>11000000)]', '{@name, @area, @population}', data)
from states
order by id;
select id, path('count(/state/region)', data)
from states
order by id;
select path('/root//a[position()=last()]', '');
select path('/root//a[position()=last() and @i]', '');
select xml.path('/root[concat("a", /root, "c", /root)="axcx"]', 'x');
select xml.path('/root[concat("a", /root, "c")="axcx"]', 'x');
select xml.path('name(/)', '');
select xml.path('name(/a/b)', '');
select xml.path('name(/a/@i)', '');
select xml.path('name(/a/node())', '');
select xml.path('/a[name()="a"]', '');
select xml.path('/a[name()!="a"]', '');
select xml.path('/a[name()="b"]', '');
select xml.path('/a[name(/)="b"]', '');
select xml.path('/a[name(/)!="b"]', '');
select xml.path('/a[name()!="b"]', '');
select xml.path('/a[name()=""]', '');
select xml.path('/a[name()!=""]', '');
-- Nested functions. In addition, result has to be implicitly cast.
select xml.path('concat(count(/root/a), /root)', '50');
select xml.path('concat(count(/root), /root)', '50');
-- DOM
select xml.children('');
select xml.element('root', '{{"i", "x"}, {"j", 1}, {"k", ""}}', '');
select xml.element('root', '{{"i", "x"}, {"j", 1}}', '');
select xml.element('root', NULL, '');
select xml.element('root', '{{"i", "x"}, {"j", 1}}', NULL);
-- xml.fragment()
WITH tmp(n) as (VALUES (''), (''), (''))
SELECT xml.fragment(n::xml.node)
FROM tmp;
-- Cleanup
drop table states;
drop table paths;
drop table docs_other;