Skip to content

Commit e10bc16

Browse files
alvherrealvherre
committed
Accept TEXT and CDATA nodes in XMLTABLE's column_expression.
Column expressions that match TEXT or CDATA nodes must return the contents of the nodes themselves, not the content of non-existing children (i.e. the empty string). Author: Markus Winand Reported-by: Markus Winand Reviewed-by: Álvaro Herrera Discussion: https://postgr.es/m/0684A598-002C-42A2-AE12-F024A324EAE4@winand.at
1 parent 99ba8d2 commit e10bc16

File tree

4 files changed

+28
-24
lines changed

4 files changed

+28
-24
lines changed

‎src/backend/utils/adt/xml.c

Lines changed: 14 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -4505,11 +4505,21 @@ XmlTableGetValue(TableFuncScanState *state, int colnum,
45054505
else if (count == 1)
45064506
{
45074507
xmlChar *str;
4508+
xmlNodePtr node;
45084509

4509-
str = xmlNodeListGetString(xtCxt->doc,
4510-
xpathobj->nodesetval->nodeTab[0]->xmlChildrenNode,
4511-
1);
4510+
/*
4511+
* Most nodes (elements and even attributes) store their data
4512+
* in children nodes. If they don't have children nodes, it
4513+
* means that they are empty (e.g. <element/>). Text nodes and
4514+
* CDATA sections are an exception: they don't have children
4515+
* but have content in the Text/CDATA node itself.
4516+
*/
4517+
node = xpathobj->nodesetval->nodeTab[0];
4518+
if (node->type != XML_CDATA_SECTION_NODE &&
4519+
node->type != XML_TEXT_NODE)
4520+
node = node->xmlChildrenNode;
45124521

4522+
str = xmlNodeListGetString(xtCxt->doc, node, 1);
45134523
if (str != NULL)
45144524
{
45154525
PG_TRY();
@@ -4526,13 +4536,7 @@ XmlTableGetValue(TableFuncScanState *state, int colnum,
45264536
}
45274537
else
45284538
{
4529-
/*
4530-
* This line ensure mapping of empty tags to PostgreSQL
4531-
* value. Usually we would to map a empty tag to empty
4532-
* string. But this mapping can create empty string when
4533-
* user doesn't expect it - when empty tag is enforced by
4534-
* libxml2 - when user uses a text() function for example.
4535-
*/
4539+
/* Ensure mapping of empty tags to PostgreSQL values. */
45364540
cstr = "";
45374541
}
45384542
}

‎src/test/regress/expected/xml.out

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1024,7 +1024,7 @@ SELECT xmltable.*
10241024
PASSING data
10251025
COLUMNS id int PATH '@id',
10261026
_id FOR ORDINALITY,
1027-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
1027+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
10281028
country_id text PATH 'COUNTRY_ID',
10291029
region_id int PATH 'REGION_ID',
10301030
size float PATH 'SIZE',
@@ -1046,7 +1046,7 @@ CREATE VIEW xmltableview1 AS SELECT xmltable.*
10461046
PASSING data
10471047
COLUMNS id int PATH '@id',
10481048
_id FOR ORDINALITY,
1049-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
1049+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
10501050
country_id text PATH 'COUNTRY_ID',
10511051
region_id int PATH 'REGION_ID',
10521052
size float PATH 'SIZE',
@@ -1075,7 +1075,7 @@ CREATE OR REPLACE VIEW public.xmltableview1 AS
10751075
"xmltable".premier_name
10761076
FROM ( SELECT xmldata.data
10771077
FROM xmldata) x,
1078-
LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
1078+
LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
10791079
EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
10801080
QUERY PLAN
10811081
-----------------------------------------
@@ -1085,15 +1085,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
10851085
(3 rows)
10861086

10871087
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
1088-
QUERY PLAN
1089-
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1088+
QUERY PLAN
1089+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10901090
Nested Loop
10911091
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
10921092
-> Seq Scan on public.xmldata
10931093
Output: xmldata.data
10941094
-> Table Function Scan on "xmltable"
10951095
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1096-
Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
1096+
Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
10971097
(7 rows)
10981098

10991099
-- XMLNAMESPACES tests

‎src/test/regress/expected/xml_2.out

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1004,7 +1004,7 @@ SELECT xmltable.*
10041004
PASSING data
10051005
COLUMNS id int PATH '@id',
10061006
_id FOR ORDINALITY,
1007-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
1007+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
10081008
country_id text PATH 'COUNTRY_ID',
10091009
region_id int PATH 'REGION_ID',
10101010
size float PATH 'SIZE',
@@ -1026,7 +1026,7 @@ CREATE VIEW xmltableview1 AS SELECT xmltable.*
10261026
PASSING data
10271027
COLUMNS id int PATH '@id',
10281028
_id FOR ORDINALITY,
1029-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
1029+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
10301030
country_id text PATH 'COUNTRY_ID',
10311031
region_id int PATH 'REGION_ID',
10321032
size float PATH 'SIZE',
@@ -1055,7 +1055,7 @@ CREATE OR REPLACE VIEW public.xmltableview1 AS
10551055
"xmltable".premier_name
10561056
FROM ( SELECT xmldata.data
10571057
FROM xmldata) x,
1058-
LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
1058+
LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
10591059
EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
10601060
QUERY PLAN
10611061
-----------------------------------------
@@ -1065,15 +1065,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
10651065
(3 rows)
10661066

10671067
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
1068-
QUERY PLAN
1069-
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1068+
QUERY PLAN
1069+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10701070
Nested Loop
10711071
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
10721072
-> Seq Scan on public.xmldata
10731073
Output: xmldata.data
10741074
-> Table Function Scan on "xmltable"
10751075
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1076-
Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
1076+
Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
10771077
(7 rows)
10781078

10791079
-- XMLNAMESPACES tests

‎src/test/regress/sql/xml.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -349,7 +349,7 @@ SELECT xmltable.*
349349
PASSING data
350350
COLUMNS id int PATH '@id',
351351
_id FOR ORDINALITY,
352-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
352+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
353353
country_id text PATH 'COUNTRY_ID',
354354
region_id int PATH 'REGION_ID',
355355
size float PATH 'SIZE',
@@ -362,7 +362,7 @@ CREATE VIEW xmltableview1 AS SELECT xmltable.*
362362
PASSING data
363363
COLUMNS id int PATH '@id',
364364
_id FOR ORDINALITY,
365-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
365+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
366366
country_id text PATH 'COUNTRY_ID',
367367
region_id int PATH 'REGION_ID',
368368
size float PATH 'SIZE',

0 commit comments

Comments
 (0)