CREATE TABLE xmltest (
id int,
data xml
);
INSERT INTO xmltest VALUES (1, 'one');
INSERT INTO xmltest VALUES (2, 'two');
INSERT INTO xmltest VALUES (3, 'one', 'xml');
SELECT pg_input_is_valid('one', 'xml');
SELECT message FROM pg_input_error_info('one', 'xml');
SELECT pg_input_is_valid('', 'xml');
SELECT message FROM pg_input_error_info('', 'xml');
SELECT xmlcomment('test');
SELECT xmlcomment('-test');
SELECT xmlcomment('test-');
SELECT xmlcomment('--test');
SELECT xmlcomment('te st');
SELECT xmlconcat(xmlcomment('hello'),
xmlelement(NAME qux, 'foo'),
xmlcomment('world'));
SELECT xmlconcat('hello', 'you');
SELECT xmlconcat(1, 2);
SELECT xmlconcat('bad', '', NULL, '');
SELECT xmlconcat('', NULL, '');
SELECT xmlconcat(NULL);
SELECT xmlconcat(NULL, NULL);
SELECT xmlelement(name element,
xmlattributes (1 as one, 'deuce' as two),
'content');
SELECT xmlelement(name element,
xmlattributes ('unnamed and wrong'));
SELECT xmlelement(name element, xmlelement(name nested, 'stuff'));
SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
SELECT xmlelement(name num, 37);
SELECT xmlelement(name foo, text 'bar');
SELECT xmlelement(name foo, xml 'bar');
SELECT xmlelement(name foo, text 'br');
SELECT xmlelement(name foo, xml 'br');
SELECT xmlelement(name foo, array[1, 2, 3]);
SET xmlbinary TO base64;
SELECT xmlelement(name foo, bytea 'bar');
SET xmlbinary TO hex;
SELECT xmlelement(name foo, bytea 'bar');
SELECT xmlelement(name foo, xmlattributes(true as bar));
SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar));
SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar));
SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'br' as funnier));
SELECT xmlparse(content '');
SELECT xmlparse(content ' ');
SELECT xmlparse(content 'abc');
SELECT xmlparse(content 'x');
SELECT xmlparse(content '&');
SELECT xmlparse(content '&idontexist;');
SELECT xmlparse(content '');
SELECT xmlparse(content '');
SELECT xmlparse(content '&idontexist;');
SELECT xmlparse(content '');
SELECT xmlparse(document ' ');
SELECT xmlparse(document 'abc');
SELECT xmlparse(document 'x');
SELECT xmlparse(document '&');
SELECT xmlparse(document '&idontexist;');
SELECT xmlparse(document '');
SELECT xmlparse(document '');
SELECT xmlparse(document '&idontexist;');
SELECT xmlparse(document '');
SELECT xmlpi(name foo);
SELECT xmlpi(name xml);
SELECT xmlpi(name xmlstuff);
SELECT xmlpi(name foo, 'bar');
SELECT xmlpi(name foo, 'in?>valid');
SELECT xmlpi(name foo, null);
SELECT xmlpi(name xml, null);
SELECT xmlpi(name xmlstuff, null);
SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"');
SELECT xmlpi(name foo, ' bar');
SELECT xmlroot(xml '', version no value, standalone no value);
SELECT xmlroot(xml '', version '2.0');
SELECT xmlroot(xml '', version no value, standalone yes);
SELECT xmlroot(xml '', version no value, standalone yes);
SELECT xmlroot(xmlroot(xml '', version '1.0'), version '1.1', standalone no);
SELECT xmlroot('', version no value, standalone no);
SELECT xmlroot('', version no value, standalone no value);
SELECT xmlroot('', version no value);
SELECT xmlroot (
xmlelement (
name gazonk,
xmlattributes (
'val' AS name,
1 + 1 AS num
),
xmlelement (
NAME qux,
'foo'
)
),
version '1.0',
standalone yes
);
SELECT xmlserialize(content data as character varying(20)) FROM xmltest;
SELECT xmlserialize(content 'good' as char(10));
SELECT xmlserialize(document 'bad' as text);
-- indent
SELECT xmlserialize(DOCUMENT '42' AS text INDENT);
SELECT xmlserialize(CONTENT '42' AS text INDENT);
-- no indent
SELECT xmlserialize(DOCUMENT '42' AS text NO INDENT);
SELECT xmlserialize(CONTENT '42' AS text NO INDENT);
-- indent non singly-rooted xml
SELECT xmlserialize(DOCUMENT '7342' AS text INDENT);
SELECT xmlserialize(CONTENT '7342' AS text INDENT);
-- indent non singly-rooted xml with mixed contents
SELECT xmlserialize(DOCUMENT 'text node73text node42' AS text INDENT);
SELECT xmlserialize(CONTENT 'text node73text node42' AS text INDENT);
-- indent singly-rooted xml with mixed contents
SELECT xmlserialize(DOCUMENT '42text node73' AS text INDENT);
SELECT xmlserialize(CONTENT '42text node73' AS text INDENT);
-- indent empty string
SELECT xmlserialize(DOCUMENT '' AS text INDENT);
SELECT xmlserialize(CONTENT '' AS text INDENT);
-- whitespaces
SELECT xmlserialize(DOCUMENT ' ' AS text INDENT);
SELECT xmlserialize(CONTENT ' ' AS text INDENT);
-- indent null
SELECT xmlserialize(DOCUMENT NULL AS text INDENT);
SELECT xmlserialize(CONTENT NULL AS text INDENT);
-- indent with XML declaration
SELECT xmlserialize(DOCUMENT '73' AS text INDENT);
SELECT xmlserialize(CONTENT '73' AS text INDENT);
-- indent containing DOCTYPE declaration
SELECT xmlserialize(DOCUMENT '' AS text INDENT);
SELECT xmlserialize(CONTENT '' AS text INDENT);
-- indent xml with empty element
SELECT xmlserialize(DOCUMENT '' AS text INDENT);
SELECT xmlserialize(CONTENT '' AS text INDENT);
-- 'no indent' = not using 'no indent'
SELECT xmlserialize(DOCUMENT '42' AS text) = xmlserialize(DOCUMENT '42' AS text NO INDENT);
SELECT xmlserialize(CONTENT '42' AS text) = xmlserialize(CONTENT '42' AS text NO INDENT);
-- indent xml strings containing blank nodes
SELECT xmlserialize(DOCUMENT ' ' AS text INDENT);
SELECT xmlserialize(CONTENT 'text node ' AS text INDENT);
SELECT xml 'bar' IS DOCUMENT;
SELECT xml 'barfoo' IS DOCUMENT;
SELECT xml '' IS NOT DOCUMENT;
SELECT xml 'abc' IS NOT DOCUMENT;
SELECT '<>' IS NOT DOCUMENT;
SELECT xmlagg(data) FROM xmltest;
SELECT xmlagg(data) FROM xmltest WHERE id > 10;
SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp;
-- Check mapping SQL identifier to XML name
SELECT xmlpi(name ":::_xml_abc135.%-&_");
SELECT xmlpi(name "123");
PREPARE foo (xml) AS SELECT xmlconcat('', $1);
SET XML OPTION DOCUMENT;
EXECUTE foo ('');
EXECUTE foo ('bad');
SELECT xml '';
SET XML OPTION CONTENT;
EXECUTE foo ('');
EXECUTE foo ('good');
SELECT xml ' ';
SELECT xml ' ';
SELECT xml '';
SELECT xml ' oops ';
SELECT xml ' ';
SELECT xml '';
-- Test backwards parsing
CREATE VIEW xmlview1 AS SELECT xmlcomment('test');
CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you');
CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&');
CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
CREATE VIEW xmlview5 AS SELECT xmlparse(content 'x');
CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar');
CREATE VIEW xmlview7 AS SELECT xmlroot(xml '', version no value, standalone yes);
CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10));
CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text);
SELECT table_name, view_definition FROM information_schema.views
WHERE table_name LIKE 'xmlview%' ORDER BY 1;
-- Text XPath expressions evaluation
SELECT xpath('/value', data) FROM xmltest;
SELECT xpath(NULL, NULL) IS NULL FROM xmltest;
SELECT xpath('', '');
SELECT xpath('//text()', 'number one');
SELECT xpath('//loc:piece/@id', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
SELECT xpath('//loc:piece', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
SELECT xpath('//loc:piece', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
SELECT xpath('//b', 'one two three etc');
SELECT xpath('//text()', '<');
SELECT xpath('//@value', '');
SELECT xpath('''<>''', '');
SELECT xpath('count(//*)', '');
SELECT xpath('count(//*)=0', '');
SELECT xpath('count(//*)=3', '');
SELECT xpath('name(/*)', '');
SELECT xpath('/nosuchtag', '');
SELECT xpath('root', '');
-- Round-trip non-ASCII data through xpath().
DO $$
DECLARE
xml_declaration text := '';
degree_symbol text;
res xml[];
BEGIN
-- Per the documentation, except when the server encoding is UTF8, xpath()
-- may not work on non-ASCII data. The untranslatable_character and
-- undefined_function traps below, currently dead code, will become relevant
-- if we remove this limitation.
IF current_setting('server_encoding') <> 'UTF8' THEN
RAISE LOG 'skip: encoding % unsupported for xpath',
current_setting('server_encoding');
RETURN;
END IF;
degree_symbol := convert_from('\xc2b0', 'UTF8');
res := xpath('text()', (xml_declaration ||
'' || degree_symbol || '')::xml);
IF degree_symbol <> res[1]::text THEN
RAISE 'expected % (%), got % (%)',
degree_symbol, convert_to(degree_symbol, 'UTF8'),
res[1], convert_to(res[1]::text, 'UTF8');
END IF;
EXCEPTION
-- character with byte sequence 0xc2 0xb0 in encoding "UTF8" has no equivalent in encoding "LATIN8"
WHEN untranslatable_character
-- default conversion function for encoding "UTF8" to "MULE_INTERNAL" does not exist
OR undefined_function
-- unsupported XML feature
OR feature_not_supported THEN
RAISE LOG 'skip: %', SQLERRM;
END
$$;
-- Test xmlexists and xpath_exists
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol');
SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol');
SELECT xmlexists('count(/nosuchtag)' PASSING BY REF '');
SELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml);
SELECT xpath_exists('//town[text() = ''Cwmbran'']','Bidford-on-AvonCwmbranBristol'::xml);
SELECT xpath_exists('count(/nosuchtag)', ''::xml);
INSERT INTO xmltest VALUES (4, ''::xml);
INSERT INTO xmltest VALUES (5, ''::xml);
INSERT INTO xmltest VALUES (6, 'BudvarfreeCarlinglots'::xml);
INSERT INTO xmltest VALUES (7, 'MolsonfreeCarlinglots'::xml);
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING data);
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING BY REF data BY REF);
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' PASSING BY REF data);
SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers/name[text() = ''Molson'']' PASSING BY REF data);
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data);
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers',data);
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers/name[text() = ''Molson'']',data);
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARRAY[ARRAY['myns','http://myns.com']]);
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers',data,ARRAY[ARRAY['myns','http://myns.com']]);
SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers/myns:name[text() = ''Molson'']',data,ARRAY[ARRAY['myns','http://myns.com']]);
CREATE TABLE query ( expr TEXT );
INSERT INTO query VALUES ('/menu/beers/cost[text() = ''lots'']');
SELECT COUNT(id) FROM xmltest, query WHERE xmlexists(expr PASSING BY REF data);
-- Test xml_is_well_formed and variants
SELECT xml_is_well_formed_document('bar');
SELECT xml_is_well_formed_document('abc');
SELECT xml_is_well_formed_content('bar');
SELECT xml_is_well_formed_content('abc');
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('abc');
SELECT xml_is_well_formed('<>');
SELECT xml_is_well_formed('');
SELECT xml_is_well_formed('bar');
SELECT xml_is_well_formed('barbaz');
SELECT xml_is_well_formed('number one');
SELECT xml_is_well_formed('bar');
SELECT xml_is_well_formed('bar');
SELECT xml_is_well_formed('&');
SELECT xml_is_well_formed('&idontexist;');
SELECT xml_is_well_formed('');
SELECT xml_is_well_formed('');
SELECT xml_is_well_formed('&idontexist;');
SET xmloption TO CONTENT;
SELECT xml_is_well_formed('abc');
-- Since xpath() deals with namespaces, it's a bit stricter about
-- what's well-formed and what's not. If we don't obey these rules
-- (i.e. ignore namespace-related errors from libxml), xpath()
-- fails in subtle ways. The following would for example produce
-- the xml value
--
-- which is invalid because '<' may not appear un-escaped in
-- attribute values.
-- Since different libxml versions emit slightly different
-- error messages, we suppress the DETAIL in this test.
\set VERBOSITY terse
SELECT xpath('/*', '');
\set VERBOSITY default
-- Again, the XML isn't well-formed for namespace purposes
SELECT xpath('/*', '');
-- XPath deprecates relative namespaces, but they're not supposed to
-- throw an error, only a warning.
SELECT xpath('/*', '');
-- External entity references should not leak filesystem information.
SELECT XMLPARSE(DOCUMENT ']>&c;');
SELECT XMLPARSE(DOCUMENT ']>&c;');
-- This might or might not load the requested DTD, but it mustn't throw error.
SELECT XMLPARSE(DOCUMENT ' ');
-- XMLPATH tests
CREATE TABLE xmldata(data xml);
INSERT INTO xmldata VALUES('
AU
Australia
3
CN
China
3
HK
HongKong
3
IN
India
3
JP
Japan
3Sinzo Abe
SG
Singapore
3791
');
-- XMLTABLE with columns
SELECT xmltable.*
FROM (SELECT data FROM xmldata) x,
LATERAL XMLTABLE('/ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
_id FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
country_id text PATH 'COUNTRY_ID',
region_id int PATH 'REGION_ID',
size float PATH 'SIZE',
unit text PATH 'SIZE/@unit',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
CREATE VIEW xmltableview1 AS SELECT xmltable.*
FROM (SELECT data FROM xmldata) x,
LATERAL XMLTABLE('/ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
_id FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
country_id text PATH 'COUNTRY_ID',
region_id int PATH 'REGION_ID',
size float PATH 'SIZE',
unit text PATH 'SIZE/@unit',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
SELECT * FROM xmltableview1;
\sv xmltableview1
EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
-- errors
SELECT * FROM XMLTABLE (ROW () PASSING null COLUMNS v1 timestamp) AS f (v1, v2);
-- XMLNAMESPACES tests
SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
'/zz:rows/zz:row'
PASSING '10
'
COLUMNS a int PATH 'zz:a');
CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
'/zz:rows/zz:row'
PASSING '10
'
COLUMNS a int PATH 'zz:a');
SELECT * FROM xmltableview2;
SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
'/rows/row'
PASSING '10
'
COLUMNS a int PATH 'a');
SELECT * FROM XMLTABLE('.'
PASSING ''
COLUMNS a text PATH 'foo/namespace::node()');
-- used in prepare statements
PREPARE pp AS
SELECT xmltable.*
FROM (SELECT data FROM xmldata) x,
LATERAL XMLTABLE('/ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
_id FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME' NOT NULL,
country_id text PATH 'COUNTRY_ID',
region_id int PATH 'REGION_ID',
size float PATH 'SIZE',
unit text PATH 'SIZE/@unit',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
EXECUTE pp;
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int);
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int);
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int);
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id');
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY);
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH '.');
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH './*');
SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text);
SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail
-- CDATA test
select * from xmltable('d/r' passing ' &"<>!foo]]>2' columns c text);
-- XML builtin entities
SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent text);
SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent xml);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT xmltable.*
FROM (SELECT data FROM xmldata) x,
LATERAL XMLTABLE('/ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
_id FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME' NOT NULL,
country_id text PATH 'COUNTRY_ID',
region_id int PATH 'REGION_ID',
size float PATH 'SIZE',
unit text PATH 'SIZE/@unit',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
-- test qual
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
EXPLAIN (VERBOSE, COSTS OFF)
SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
-- should to work with more data
INSERT INTO xmldata VALUES('
CZ
Czech Republic
2Milos Zeman
DE
Germany
2
FR
France
2
');
INSERT INTO xmldata VALUES('
EG
Egypt
1
SD
Sudan
1
');
SELECT xmltable.*
FROM (SELECT data FROM xmldata) x,
LATERAL XMLTABLE('/ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
_id FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME' NOT NULL,
country_id text PATH 'COUNTRY_ID',
region_id int PATH 'REGION_ID',
size float PATH 'SIZE',
unit text PATH 'SIZE/@unit',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
SELECT xmltable.*
FROM (SELECT data FROM xmldata) x,
LATERAL XMLTABLE('/ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
_id FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME' NOT NULL,
country_id text PATH 'COUNTRY_ID',
region_id int PATH 'REGION_ID',
size float PATH 'SIZE',
unit text PATH 'SIZE/@unit',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
WHERE region_id = 2;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT xmltable.*
FROM (SELECT data FROM xmldata) x,
LATERAL XMLTABLE('/ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
_id FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME' NOT NULL,
country_id text PATH 'COUNTRY_ID',
region_id int PATH 'REGION_ID',
size float PATH 'SIZE',
unit text PATH 'SIZE/@unit',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
WHERE region_id = 2;
-- should fail, NULL value
SELECT xmltable.*
FROM (SELECT data FROM xmldata) x,
LATERAL XMLTABLE('/ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
_id FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME' NOT NULL,
country_id text PATH 'COUNTRY_ID',
region_id int PATH 'REGION_ID',
size float PATH 'SIZE' NOT NULL,
unit text PATH 'SIZE/@unit',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
-- if all is ok, then result is empty
-- one line xml test
WITH
x AS (SELECT proname, proowner, procost::numeric, pronargs,
array_to_string(proargnames,',') as proargnames,
case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes
FROM pg_proc WHERE proname = 'f_leak'),
y AS (SELECT xmlelement(name proc,
xmlforest(proname, proowner,
procost, pronargs,
proargnames, proargtypes)) as proc
FROM x),
z AS (SELECT xmltable.*
FROM y,
LATERAL xmltable('/proc' PASSING proc
COLUMNS proname name,
proowner oid,
procost float,
pronargs int,
proargnames text,
proargtypes text))
SELECT * FROM z
EXCEPT SELECT * FROM x;
-- multi line xml test, result should be empty too
WITH
x AS (SELECT proname, proowner, procost::numeric, pronargs,
array_to_string(proargnames,',') as proargnames,
case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes
FROM pg_proc),
y AS (SELECT xmlelement(name data,
xmlagg(xmlelement(name proc,
xmlforest(proname, proowner, procost,
pronargs, proargnames, proargtypes)))) as doc
FROM x),
z AS (SELECT xmltable.*
FROM y,
LATERAL xmltable('/data/proc' PASSING doc
COLUMNS proname name,
proowner oid,
procost float,
pronargs int,
proargnames text,
proargtypes text))
SELECT * FROM z
EXCEPT SELECT * FROM x;
CREATE TABLE xmltest2(x xml, _path text);
INSERT INTO xmltest2 VALUES('1', 'A');
INSERT INTO xmltest2 VALUES('2', 'B');
INSERT INTO xmltest2 VALUES('3', 'C');
INSERT INTO xmltest2 VALUES('2', 'D');
SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c');
SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.');
SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
-- XPath result can be boolean or number too
SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"', e integer PATH 'string-length(.)');
\x
SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH '/e/n2', y xml PATH '/');
\x
SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '""', b xml PATH '""');
SELECT xmltext(NULL);
SELECT xmltext('');
SELECT xmltext(' ');
SELECT xmltext('foo `$_-+?=*^%!|/\()[]{}');
SELECT xmltext('foo & <"bar">');
SELECT xmltext('x'|| '73
'::xml || .42 || true || 'j'::char);