Re: [PoC] XMLCast (SQL/XML X025)
On 05.07.24 16:18, Jim Jones wrote:
> On 02.07.24 18:02, Jim Jones wrote:
>> It basically does the following:
>>
>> * When casting an XML value to a SQL data type, XML values containing
>> XSD literals will be converted to their equivalent SQL data type.
>> * When casting from a SQL data type to XML, the cast operand will be
>> translated to its corresponding XSD data type.
>>
> v2 attached adds missing return for NO_XML_SUPPORT control path in
> unescape_xml
>
v3 adds the missing XML passing mechanism BY VALUE and BY REF, as
described in the XMLCast specification:
XMLCAST ( AS [ ])
Tests and documentation were updated accordingly.
--
Jim
From 0679e82e9653183190a6af6c97de1887f567ef72 Mon Sep 17 00:00:00 2001
From: Jim Jones
Date: Thu, 15 Aug 2024 20:27:36 +0200
Subject: [PATCH v3] Add XMLCast function (SQL/XML X025)
This function implements the SQL/XML function xmlcast, which
enables conversions between SQL data types and the XML data type.
XMLCAST ( expression AS type [ BY REF | BY VALUE ] )
When casting an XML value to a SQL data type, XML values containing
XSD literals will be converted to their equivalent SQL data type.
When casting from a SQL data type to XML, the cast operand will be
translated to its corresponding XSD data type.
This patch also includes documentation and regression tests.
---
doc/src/sgml/datatype.sgml| 78 -
src/backend/catalog/sql_features.txt | 2 +-
src/backend/executor/execExprInterp.c | 83 -
src/backend/nodes/nodeFuncs.c | 13 +
src/backend/parser/gram.y | 22 +-
src/backend/parser/parse_expr.c | 81 +
src/backend/parser/parse_target.c | 7 +
src/backend/utils/adt/ruleutils.c | 4 +
src/backend/utils/adt/xml.c | 29 ++
src/include/nodes/parsenodes.h| 8 +
src/include/nodes/primnodes.h | 3 +
src/include/parser/kwlist.h | 1 +
src/include/utils/xml.h | 1 +
src/test/regress/expected/xml.out | 429 ++
src/test/regress/expected/xml_1.out | 346 +
src/test/regress/expected/xml_2.out | 429 ++
src/test/regress/sql/xml.sql | 210 +
src/tools/pgindent/typedefs.list | 1 +
18 files changed, 1739 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index e0d33f12e1..28c93460a5 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4472,14 +4472,84 @@ XMLPARSE ( { DOCUMENT | CONTENT } value)
XMLPARSE (DOCUMENT 'Manual...')
XMLPARSE (CONTENT 'abcbarfoo')
]]>
-While this is the only way to convert character strings into XML
-values according to the SQL standard, the PostgreSQL-specific
-syntaxes:
+
+Another option to convert character strings into xml is the function xmlcast,
+which is designed to cast SQL data types into xml, and vice versa.
+
+XMLCAST ( expression AS type [ BY REF | BY VALUE ] )
+
+Similar to the SQL function CAST, this function converts an expression
+into the specified type. This can be useful for creating XML
+documents using SQL or when parsing the contents of XML documents. The function xmlcast works with the
+following criteria:
+
+
+
+
+ Either expression or type must be of type xml.
+
+
+
+
+ It supports casting between xml and character, numeric, date/time, and boolean data types.
+
+
+
+
+ Similar to the function xmltext, expressions containing XML predifined entities
+ will be escaped (see examples below).
+
+
+
+
+ The expressions of type date, time [with time zone], timestamp [with time zone],
+ and interval will be converted to their XSD equivalents, xs:date, xs:time,
+ xs:dateTime, and xs:duration, respectively.
+
+
+
+
+ The BY REF and BY VALUE clauses
+ are accepted but ignored, as discussed in
+ .
+
+
+
+
+ Examples:
+
+
+Alternatively, it is also possible to convert character strings into XML using PostgreSQL-specific cast syntaxes:
-can also be used.
+
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index c002f37202..a4d8f7a2ac 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -624,7 +624,7 @@ X014 Attributes of XML type YES
X015 Fields of XML type NO
X016 Persistent XML values YES
X020 XMLConcat YES
-X025 XMLCast NO
+X025 XMLCast YES
X030 XMLDocument NO
X031 XMLElement YES
X032 XMLForest YES
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index ea47c4d6f9..1eb2231aa1 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -69,6 +69,7 @@
Re: [PoC] XMLCast (SQL/XML X025)
On 02.07.24 18:02, Jim Jones wrote:
> It basically does the following:
>
> * When casting an XML value to a SQL data type, XML values containing
> XSD literals will be converted to their equivalent SQL data type.
> * When casting from a SQL data type to XML, the cast operand will be
> translated to its corresponding XSD data type.
>
v2 attached adds missing return for NO_XML_SUPPORT control path in
unescape_xml
--
Jim
From e30ded6ea7b3a18e2c26150ebd415a2853bad094 Mon Sep 17 00:00:00 2001
From: Jim Jones
Date: Fri, 5 Jul 2024 15:26:09 +0200
Subject: [PATCH v2] Add XMLCast function (SQL/XML X025)
This function implements the SQL/XML function xmlcast, which
enables conversions between SQL data types and the XML data type.
When casting an XML value to a SQL data type, XML values containing
XSD literals will be converted to their equivalent SQL data type.
When casting from a SQL data type to XML, the cast operand will be
translated to its corresponding XSD data type.
This patch also includes documentation and regression tests.
---
doc/src/sgml/datatype.sgml| 71 +-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/executor/execExprInterp.c | 83 ++-
src/backend/nodes/nodeFuncs.c | 13 +
src/backend/parser/gram.y | 13 +-
src/backend/parser/parse_expr.c | 82 +++
src/backend/parser/parse_target.c | 7 +
src/backend/utils/adt/ruleutils.c | 4 +
src/backend/utils/adt/xml.c | 29 +++
src/include/nodes/parsenodes.h| 8 +
src/include/nodes/primnodes.h | 3 +
src/include/parser/kwlist.h | 1 +
src/include/utils/xml.h | 1 +
src/test/regress/expected/xml.out | 326 ++
src/test/regress/expected/xml_1.out | 258
src/test/regress/expected/xml_2.out | 326 ++
src/test/regress/sql/xml.sql | 152
src/tools/pgindent/typedefs.list | 1 +
18 files changed, 1372 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 6646820d6a..3bce8eb674 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4459,14 +4459,77 @@ XMLPARSE ( { DOCUMENT | CONTENT } value)
XMLPARSE (DOCUMENT 'Manual...')
XMLPARSE (CONTENT 'abcbarfoo')
]]>
-While this is the only way to convert character strings into XML
-values according to the SQL standard, the PostgreSQL-specific
-syntaxes:
+
+Another option to convert character strings into xml is the function xmlcast,
+which is designed to cast SQL data types into xml, and vice versa.
+
+XMLCAST ( expression AS type )
+
+Similar to the SQL function CAST, this function converts an expression
+into the specified type. This can be useful for creating XML
+documents using SQL or when parsing the contents of XML documents. The function xmlcast works with the
+following criteria:
+
+
+
+
+ Either expression or type must be of type xml.
+
+
+
+
+ It supports casting between xml and character, numeric, date/time, and boolean data types.
+
+
+
+
+ Similar to the function xmltext, expressions containing XML predifined entities
+ will be escaped (see examples below).
+
+
+
+
+ The expressions of type date, time [with time zone], timestamp [with time zone],
+ and interval will be converted to their XSD equivalents, xs:date, xs:time,
+ xs:dateTime, and xs:duration, respectively.
+
+
+
+
+ Examples:
+
+
+Alternatively, it is also possible to convert character strings into XML using PostgreSQL-specific cast syntaxes:
-can also be used.
+
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index c002f37202..a4d8f7a2ac 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -624,7 +624,7 @@ X014 Attributes of XML type YES
X015 Fields of XML type NO
X016 Persistent XML values YES
X020 XMLConcat YES
-X025 XMLCast NO
+X025 XMLCast YES
X030 XMLDocument NO
X031 XMLElement YES
X032 XMLForest YES
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index d8735286c4..5a60627b6f 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -69,6 +69,7 @@
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/date.h"
+#include "utils/datetime.h"
#include "utils/datum.h"
#include "utils/expandedrecord.h"
#include "utils/json.h"
@@ -4075,10 +4076,88 @@ ExecEvalXmlExpr(ExprState *state, ExprEvalStep *op)
*op->resnull = false;
}
break;
+ case IS_XMLCAST:
+ {
+Datum *argvalue = op->d.xmlexpr.argvalue;
+bool *argnull = op->d.xmlexpr.argnull;
+char *str;
[PoC] XMLCast (SQL/XML X025)
Hi,
This is a PoC that implements XMLCast (SQL/XML X025), which enables
conversions between SQL and XML data type.
It basically does the following:
* When casting an XML value to a SQL data type, XML values containing
XSD literals will be converted to their equivalent SQL data type.
* When casting from a SQL data type to XML, the cast operand will be
translated to its corresponding XSD data type.
SELECT xmlcast(now() AS xml);
xmlcast
--
2024-07-02T17:03:11.189073+02:00
(1 row)
SELECT xmlcast('2024-07-02T17:03:11.189073+02:00'::xml AS timestamp with
time zone);
xmlcast
---
2024-07-02 17:03:11.189073+02
(1 row)
SELECT xmlcast('P1Y2M3DT4H5M6S'::xml AS interval);
xmlcast
---
1 year 2 mons 3 days 04:05:06
(1 row)
SELECT xmlcast(''::xml AS text);
xmlcast
---
(1 row)
SELECT xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6
seconds'::interval AS xml) ;
xmlcast
P1Y2M3DT4H5M6S
(1 row)
SELECT xmlcast('42.73'::xml AS numeric);
xmlcast
-
42.73
(1 row)
SELECT xmlcast(42730102030405 AS xml);
xmlcast
42730102030405
(1 row)
Is it starting in the right direction? Any feedback would be much
appreciated.
Best,
JimFrom 945a18ba2ee20b32e5694c4285f44a70c913151b Mon Sep 17 00:00:00 2001
From: Jim Jones
Date: Wed, 19 Jun 2024 09:11:39 +0200
Subject: [PATCH v1] Add XMLCast function (SQL/XML X025)
This function implements the SQL/XML function xmlcast, which
enables conversions between SQL data types and the XML data type.
When casting an XML value to a SQL data type, XML values containing
XSD literals will be converted to their equivalent SQL data type.
When casting from a SQL data type to XML, the cast operand will be
translated to its corresponding XSD data type.
This patch also includes documentation and regression tests.
---
doc/src/sgml/datatype.sgml| 71 +-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/executor/execExprInterp.c | 83 ++-
src/backend/nodes/nodeFuncs.c | 13 +
src/backend/parser/gram.y | 13 +-
src/backend/parser/parse_expr.c | 82 +++
src/backend/parser/parse_target.c | 7 +
src/backend/utils/adt/ruleutils.c | 4 +
src/backend/utils/adt/xml.c | 28 +++
src/include/nodes/parsenodes.h| 8 +
src/include/nodes/primnodes.h | 3 +
src/include/parser/kwlist.h | 1 +
src/include/utils/xml.h | 1 +
src/test/regress/expected/xml.out | 326 ++
src/test/regress/expected/xml_1.out | 258
src/test/regress/expected/xml_2.out | 326 ++
src/test/regress/sql/xml.sql | 152
src/tools/pgindent/typedefs.list | 1 +
18 files changed, 1371 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 6646820d6a..3bce8eb674 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4459,14 +4459,77 @@ XMLPARSE ( { DOCUMENT | CONTENT } value)
XMLPARSE (DOCUMENT 'Manual...')
XMLPARSE (CONTENT 'abcbarfoo')
]]>
-While this is the only way to convert character strings into XML
-values according to the SQL standard, the PostgreSQL-specific
-syntaxes:
+
+Another option to convert character strings into xml is the function xmlcast,
+which is designed to cast SQL data types into xml, and vice versa.
+
+XMLCAST ( expression AS type )
+
+Similar to the SQL function CAST, this function converts an expression
+into the specified type. This can be useful for creating XML
+documents using SQL or when parsing the contents of XML documents. The function xmlcast works with the
+following criteria:
+
+
+
+
+ Either expression or type must be of type xml.
+
+
+
+
+ It supports casting between xml and character, numeric, date/time, and boolean data types.
+
+
+
+
+ Similar to the function xmltext, expressions containing XML predifined entities
+ will be escaped (see examples below).
+
+
+
+
+ The expressions of type date, time [with time zone], timestamp [with time zone],
+ and interval will be converted to their XSD equivalents, xs:date, xs:time,
+ xs:dateTime, and xs:duration, respectively.
+
+
+
+
+ Examples:
+
+
+Alternatively, it is also possible to convert character strings into XML using PostgreSQL-specific cast syntaxes:
-can also be used.
+
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index c002f37202..a4d8f7a2ac 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog
