v9-0001-Add-XMLCast-function-SQL-XML-X025.patch

From 407ef65a398aa2d968d7b3d7d86548e3edcddd99 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Mon, 19 May 2025 14:57:27 +0200
Subject: [PATCH v9] Add XMLCast function (SQL/XML X025)

This 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     |  10 +-
 src/backend/utils/adt/xml.c           |  61 +++
 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     | 565 +++++++++++++++++++++++++
 src/test/regress/expected/xml_1.out   | 429 +++++++++++++++++++
 src/test/regress/expected/xml_2.out   | 567 ++++++++++++++++++++++++++
 src/test/regress/sql/xml.sql          | 278 +++++++++++++
 src/tools/pgindent/typedefs.list      |   1 +
 18 files changed, 2201 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 09309ba039..f1b2f91924 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4496,14 +4496,84 @@ XMLPARSE ( { DOCUMENT | CONTENT } <replaceable>value</replaceable>)
 XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
 XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')
 ]]></programlisting>
-    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 <function>xmlcast</function>,
+    which is designed to cast SQL data types into <type>xml</type>, and vice versa.
+<synopsis>
+XMLCAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> [ BY REF | BY VALUE ] )
+</synopsis>
+    Similar to the SQL function <function>CAST</function>, this function converts an <replaceable>expression</replaceable>
+    into the specified <replaceable>type</replaceable>. This can be useful for creating XML
+    documents using SQL or when parsing the contents of XML documents. The function <function>xmlcast</function> works with the
+    following criteria:
+
+     <itemizedlist>
+      <listitem>
+        <para>
+          Either <replaceable>expression</replaceable> or <replaceable>type</replaceable> must be of type <literal>xml</literal>.
+        </para>
+      </listitem>
+      <listitem>
+        <para>
+          It supports casting between <literal>xml</literal> and character, numeric, date/time, and boolean data types.
+        </para>
+      </listitem>
+      <listitem>
+        <para>
+          Similar to the function <function>xmltext</function>, <replaceable>expression</replaceable>s containing XML predifined entities
+          will be escaped (see examples below).
+        </para>
+      </listitem>
+      <listitem>
+        <para>
+          The <replaceable>expression</replaceable>s of type <literal>date</literal>, <literal>time [with time zone]</literal>, <literal>timestamp [with time zone]</literal>,
+          and <literal>interval</literal> will be converted to their XSD equivalents, <literal>xs:date</literal>, <literal>xs:time</literal>,
+          <literal>xs:dateTime</literal>, and <literal>xs:duration</literal>, respectively.
+        </para>
+      </listitem>
+       <listitem>
+        <para>
+          The <literal>BY REF</literal> and <literal>BY VALUE</literal> clauses
+          are accepted but ignored, as discussed in
+          <xref linkend="functions-xml-limits-postgresql"/>.
+        </para>
+      </listitem>
+    </itemizedlist>
+
+     Examples:
+<screen><![CDATA[
+SELECT xmlcast('<foo&bar>'::text AS xml);
+       xmlcast
+---------------------
+ &lt;foo&amp;bar&gt;
+
+SELECT xmlcast('&lt;foo&amp;bar&gt;'::xml AS text);
+  xmlcast
+-----------
+ <foo&bar>
+
+SELECT xmlcast(CURRENT_TIMESTAMP AS xml);
+             xmlcast
+---------------------------------
+ 2024-06-02T00:29:40.92397+02:00
+
+SELECT xmlcast('P1Y2M3W4DT5H6M7S'::xml AS interval);
+            xmlcast
+--------------------------------
+ 1 year 2 mons 25 days 05:06:07
+
+SELECT xmlcast('1 year 2 months 3 weeks 4 days 5 hours 6 minutes 7 seconds'::interval AS xml);
+     xmlcast
+-----------------
+ P1Y2M25DT5H6M7S
+]]></screen>
+
+    Alternatively, it is also possible to convert character strings into XML using PostgreSQL-specific cast syntaxes:
 <programlisting><![CDATA[
 xml '<foo>bar</foo>'
 '<foo>bar</foo>'::xml
 ]]></programlisting>
-    can also be used.
+
    </para>
 
    <para>
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ebe85337c2..5f34d95d64 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 8a72b5e70a..a1c2e529f1 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"
@@ -4643,10 +4644,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;
 
-		default:
-			elog(ERROR, "unrecognized XML operation");
+				Assert(list_length(xexpr->args) == 1);
+
+				if (argnull[0])
+					return;
+
+				value = argvalue[0];
+
+				switch (xexpr->targetType)
+				{
+				case XMLOID:
+					/* These data types must be converted to their ISO 8601 representations */
+					if (xexpr->type == TIMESTAMPOID || xexpr->type == TIMESTAMPTZOID ||
+						xexpr->type == DATEOID || xexpr->type == BYTEAOID || xexpr->type == BOOLOID)
+					{
+						text *mapped_value = cstring_to_text(
+							map_sql_value_to_xml_value(value, xexpr->type, false));
+						*op->resvalue = PointerGetDatum(mapped_value);
+					}
+					/* INTERVAL data nust be converted to ISO 8601, e.g. '1 year 2 mons' -> 'P1Y2M' */
+					else if (xexpr->type == INTERVALOID)
+					{
+						Interval *in = DatumGetIntervalP(value);
+
+						struct pg_itm tt, *itm = &tt;
+						char buf[MAXDATELEN + 1];
+
+						if (INTERVAL_NOT_FINITE(in))
+						{
+							if (INTERVAL_IS_NOBEGIN(in))
+								strcpy(buf, EARLY);
+							else if (INTERVAL_IS_NOEND(in))
+								strcpy(buf, LATE);
+							else
+								elog(ERROR, "invalid interval argument");
+						}
+						else
+						{
+							interval2itm(*in, itm);
+							EncodeInterval(itm, INTSTYLE_ISO_8601, buf);
+						}
+
+						*op->resvalue = PointerGetDatum(cstring_to_text(buf));
+					}
+					/* no need to escape the result, as the origin is also an XML */
+					else if (xexpr->type == XMLOID)
+						*op->resvalue = PointerGetDatum(DatumGetXmlP(value));
+					/* we  make sure that potential predifined entitties are escaped */
+					else
+						*op->resvalue = PointerGetDatum(
+							DatumGetXmlP((DirectFunctionCall1(xmltext, value))));
+					break;
+				case TEXTOID:
+				case VARCHAROID:
+				case NAMEOID:
+				case BPCHAROID:
+					/*
+					 * when casting from XML to a character string we make sure that
+					 * all escaped xml characters are unescaped.
+					 */
+					str = text_to_cstring(DatumGetTextPP(value));
+					*op->resvalue = PointerGetDatum(
+						cstring_to_text(unescape_xml(str)));
+
+					pfree(str);
+					break;
+				default:
+					*op->resvalue = PointerGetDatum(DatumGetTextP(value));
+					break;
+				}
+
+				*op->resnull = false;
+			}
 			break;
+			default:
+				elog(ERROR, "unrecognized XML operation");
+				break;
 	}
 }
 
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 7bc823507f..88c0a53e53 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1736,6 +1736,9 @@ exprLocation(const Node *expr)
 		case T_FunctionParameter:
 			loc = ((const FunctionParameter *) expr)->location;
 			break;
+		case T_XmlCast:
+			loc = ((const XmlCast *) expr)->location;
+			break;
 		case T_XmlSerialize:
 			/* XMLSERIALIZE keyword should always be the first thing */
 			loc = ((const XmlSerialize *) expr)->location;
@@ -4468,6 +4471,16 @@ raw_expression_tree_walker_impl(Node *node,
 					return true;
 			}
 			break;
+		case T_XmlCast:
+			{
+				XmlCast   *xc = (XmlCast *) node;
+
+				if (WALK(xc->expr))
+					return true;
+				if (WALK(xc->targetType))
+					return true;
+			}
+			break;
 		case T_CollateClause:
 			return WALK(((CollateClause *) node)->arg);
 		case T_SortBy:
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0b5652071d..5767a69219 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -788,7 +788,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 	WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
 
-	XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLNAMESPACES
+	XML_P XMLATTRIBUTES XMLCAST XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLNAMESPACES
 	XMLPARSE XMLPI XMLROOT XMLSERIALIZE XMLTABLE
 
 	YEAR_P YES_P
@@ -16053,6 +16053,24 @@ func_expr_common_subexpr:
 					v->location = @1;
 					$$ = (Node *) v;
 				}
+			| XMLCAST '(' a_expr AS Typename ')'
+				{
+					XmlCast *n = makeNode(XmlCast);
+
+					n->expr = $3;
+					n->targetType = $5;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+			| XMLCAST '(' a_expr AS Typename xml_passing_mech')'
+				{
+					XmlCast *n = makeNode(XmlCast);
+
+					n->expr = $3;
+					n->targetType = $5;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
 			| XMLCONCAT '(' expr_list ')'
 				{
 					$$ = makeXmlExpr(IS_XMLCONCAT, NULL, NIL, $3, @1);
@@ -18106,6 +18124,7 @@ col_name_keyword:
 			| VALUES
 			| VARCHAR
 			| XMLATTRIBUTES
+			| XMLCAST
 			| XMLCONCAT
 			| XMLELEMENT
 			| XMLEXISTS
@@ -18694,6 +18713,7 @@ bare_label_keyword:
 			| WRITE
 			| XML_P
 			| XMLATTRIBUTES
+			| XMLCAST
 			| XMLCONCAT
 			| XMLELEMENT
 			| XMLEXISTS
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 1f8e2d5467..6041248b6c 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -40,6 +40,10 @@
 #include "utils/timestamp.h"
 #include "utils/xml.h"
 
+#define type_is_xmlcast_supported(x) ((x) == TYPCATEGORY_NUMERIC || (x) == TYPCATEGORY_STRING \
+			|| (x) == TYPCATEGORY_DATETIME || (x) == TYPCATEGORY_BOOLEAN || (x) == TYPCATEGORY_UNKNOWN \
+			|| (x) == TYPCATEGORY_TIMESPAN)
+
 /* GUC parameters */
 bool		Transform_null_equals = false;
 
@@ -67,6 +71,7 @@ static Node *transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m);
 static Node *transformSQLValueFunction(ParseState *pstate,
 									   SQLValueFunction *svf);
 static Node *transformXmlExpr(ParseState *pstate, XmlExpr *x);
+static Node *transformXmlCast(ParseState *pstate, XmlCast *xc);
 static Node *transformXmlSerialize(ParseState *pstate, XmlSerialize *xs);
 static Node *transformBooleanTest(ParseState *pstate, BooleanTest *b);
 static Node *transformCurrentOfExpr(ParseState *pstate, CurrentOfExpr *cexpr);
@@ -274,6 +279,10 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 											   (SQLValueFunction *) expr);
 			break;
 
+		case T_XmlCast:
+			result = transformXmlCast(pstate, (XmlCast *) expr);
+			break;
+
 		case T_XmlExpr:
 			result = transformXmlExpr(pstate, (XmlExpr *) expr);
 			break;
@@ -2462,6 +2471,10 @@ transformXmlExpr(ParseState *pstate, XmlExpr *x)
 					newe = coerce_to_specific_type(pstate, newe, INT4OID,
 												   "XMLROOT");
 				break;
+			case IS_XMLCAST:
+				/* not handled here */
+				Assert(false);
+				break;
 			case IS_XMLSERIALIZE:
 				/* not handled here */
 				Assert(false);
@@ -2478,6 +2491,74 @@ transformXmlExpr(ParseState *pstate, XmlExpr *x)
 	return (Node *) newx;
 }
 
+static Node *
+transformXmlCast(ParseState *pstate, XmlCast *xc)
+{
+	Node *result;
+	XmlExpr *xexpr;
+	int32 targetTypmod;
+	Oid targetType;
+	Oid inputType;
+	char inputTypcategory;
+	char targetTypcategory;
+	bool inputTypispreferred;
+	bool targetTypispreferred;
+
+	/*
+	 * we make sure that either the cast operand or the data type is an XML,
+	 * and check if the data types are supported.
+	 */
+	inputType = exprType(transformExprRecurse(pstate, xc->expr));
+	get_type_category_preferred(inputType, &inputTypcategory, &inputTypispreferred);
+
+	typenameTypeIdAndMod(pstate, xc->targetType, &targetType, &targetTypmod);
+	get_type_category_preferred(targetType, &targetTypcategory, &targetTypispreferred);
+
+	if ((inputType != XMLOID && targetType != XMLOID) ||
+		(!type_is_xmlcast_supported(inputTypcategory) && inputType != XMLOID && inputType != BYTEAOID) ||
+		(!type_is_xmlcast_supported(targetTypcategory) && targetType != XMLOID && targetType != BYTEAOID))
+		ereport(ERROR,
+				(errcode(ERRCODE_CANNOT_COERCE),
+				 errmsg("cannot cast from '%s' to '%s'",
+						format_type_be(inputType), format_type_be(targetType)),
+				 parser_errposition(pstate, xc->location)));
+
+	/*
+	 * it is not possible to cast some supported data types directly to XML,
+	 * so we first handle them as TEXT and later on return them as XML.
+	 */
+	if (inputTypcategory == TYPCATEGORY_NUMERIC || inputType == TIMEOID ||
+		inputType == TIMETZOID || inputType == UNKNOWNOID || inputType == NAMEOID)
+		inputType = TEXTOID;
+
+	xexpr = makeNode(XmlExpr);
+	xexpr->op = IS_XMLCAST;
+	xexpr->location = xc->location;
+	xexpr->type = inputType;
+	xexpr->targetType = targetType;
+	xexpr->name = "xmlcast";
+	xexpr->args = list_make1(coerce_to_specific_type(pstate,
+													 transformExprRecurse(pstate, xc->expr),
+													 inputType,
+													 "XMLCAST"));
+
+	result = coerce_to_target_type(pstate, (Node *) xexpr,
+								   targetType == TEXTOID ? XMLOID : TEXTOID,
+								   targetType, targetTypmod,
+								   COERCION_EXPLICIT,
+								   COERCE_EXPLICIT_CAST,
+								   -1);
+
+	if (result == NULL)
+		ereport(ERROR,
+				(errcode(ERRCODE_CANNOT_COERCE),
+				 errmsg("cannot cast XMLCAST result to %s",
+						format_type_be(targetType)),
+				 parser_errposition(pstate, xexpr->location)));
+
+	return result;
+}
+
 static Node *
 transformXmlSerialize(ParseState *pstate, XmlSerialize *xs)
 {
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 4aba0d9d4d..6edc0a19c5 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1956,6 +1956,9 @@ FigureColnameInternal(Node *node, char **name)
 			/* make SQL/XML functions act like a regular function */
 			switch (((XmlExpr *) node)->op)
 			{
+				case IS_XMLCAST:
+					*name = "xmlcast";
+					return 2;
 				case IS_XMLCONCAT:
 					*name = "xmlconcat";
 					return 2;
@@ -1982,6 +1985,10 @@ FigureColnameInternal(Node *node, char **name)
 					break;
 			}
 			break;
+		case T_XmlCast:
+			/* make XMLCAST act like a regular function */
+			*name = "xmlcast";
+			return 2;
 		case T_XmlSerialize:
 			/* make XMLSERIALIZE act like a regular function */
 			*name = "xmlserialize";
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 467b08198b..cd5581b6ef 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10081,6 +10081,9 @@ get_rule_expr(Node *node, deparse_context *context,
 					case IS_XMLSERIALIZE:
 						appendStringInfoString(buf, "XMLSERIALIZE(");
 						break;
+					case IS_XMLCAST:
+						appendStringInfoString(buf, "XMLCAST(");
+						break;
 					case IS_DOCUMENT:
 						break;
 				}
@@ -10091,7 +10094,7 @@ get_rule_expr(Node *node, deparse_context *context,
 					else
 						appendStringInfoString(buf, "CONTENT ");
 				}
-				if (xexpr->name)
+				if (xexpr->name && xexpr->op != IS_XMLCAST)
 				{
 					appendStringInfo(buf, "NAME %s",
 									 quote_identifier(map_xml_name_to_sql_identifier(xexpr->name)));
@@ -10127,6 +10130,7 @@ get_rule_expr(Node *node, deparse_context *context,
 						appendStringInfoString(buf, ", ");
 					switch (xexpr->op)
 					{
+						case IS_XMLCAST:
 						case IS_XMLCONCAT:
 						case IS_XMLELEMENT:
 						case IS_XMLFOREST:
@@ -10204,6 +10208,10 @@ get_rule_expr(Node *node, deparse_context *context,
 						appendStringInfoString(buf, " NO INDENT");
 				}
 
+				if (xexpr->op == IS_XMLCAST)
+					appendStringInfo(buf, " AS %s",
+									 format_type_be(xexpr->targetType));
+
 				if (xexpr->op == IS_DOCUMENT)
 					appendStringInfoString(buf, " IS DOCUMENT");
 				else
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index db8d0d6a7e..24fca428f6 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -2722,6 +2722,67 @@ escape_xml(const char *str)
 	return buf.data;
 }
 
+/*
+ * Unescape XML escaped characters.
+ *
+ * In order to keep it consistent with "escape_xml(const char*)",
+ * this function intentionally does not depend on libxml2.
+ */
+char *
+unescape_xml(const char *str)
+{
+	StringInfoData buf;
+	size_t p = 0;
+	size_t len;
+
+	if (!str)
+		return NULL;
+
+	len = strlen(str);
+
+	initStringInfo(&buf);
+
+	while (p < len)
+	{
+		if (p + 4 <= len && strncmp(str + p, "&lt;", 4) == 0)
+		{
+			appendStringInfoChar(&buf, '<');
+			p += 4;
+		}
+		else if (p + 4 <= len && strncmp(str + p, "&gt;", 4) == 0)
+		{
+			appendStringInfoChar(&buf, '>');
+			p += 4;
+		}
+		else if (p + 5 <= len && strncmp(str + p, "&amp;", 5) == 0)
+		{
+			appendStringInfoChar(&buf, '&');
+			p += 5;
+		}
+		else if (p + 5 <= len && strncmp(str + p, "&#13;", 5) == 0)
+		{
+			appendStringInfoChar(&buf, '\r');
+			p += 5;
+		}
+		else if (p + 6 <= len && strncmp(str + p, "&quot;", 6) == 0)
+		{
+			appendStringInfoChar(&buf, '"');
+			p += 6;
+		}
+		else if (p + 6 <= len && strncmp(str + p, "&#x0D;", 6) == 0)
+		{
+			appendStringInfoChar(&buf, '\r');
+			p += 6;
+		}
+		else
+		{
+			appendStringInfoChar(&buf, *(str + p));
+			p++;
+		}
+	}
+
+	return buf.data;
+}
 
 static char *
 _SPI_strdup(const char *s)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4610fc6129..d3d82a6e7f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -860,6 +860,14 @@ typedef struct XmlSerialize
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } XmlSerialize;
 
+typedef struct XmlCast
+{
+	NodeTag		type;
+	Node	   *expr;
+	TypeName   *targetType;
+	ParseLoc	location;		/* token location, or -1 if unknown */
+} XmlCast;
+
 /* Partitioning related definitions */
 
 /*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 7d3b4198f2..8cc25f7f56 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1582,6 +1582,7 @@ typedef struct SQLValueFunction
  */
 typedef enum XmlExprOp
 {
+	IS_XMLCAST,					/* XMLCAST(op AS datatype) */
 	IS_XMLCONCAT,				/* XMLCONCAT(args) */
 	IS_XMLELEMENT,				/* XMLELEMENT(name, xml_attributes, args) */
 	IS_XMLFOREST,				/* XMLFOREST(xml_attributes) */
@@ -1618,6 +1619,8 @@ typedef struct XmlExpr
 	/* target type/typmod for XMLSERIALIZE */
 	Oid			type pg_node_attr(query_jumble_ignore);
 	int32		typmod pg_node_attr(query_jumble_ignore);
+	/* option for XMLCAST */
+	Oid 		targetType;
 	/* token location, or -1 if unknown */
 	ParseLoc	location;
 } XmlExpr;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a..65750625b1 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -506,6 +506,7 @@ PG_KEYWORD("wrapper", WRAPPER, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("write", WRITE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("xml", XML_P, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("xmlattributes", XMLATTRIBUTES, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("xmlcast", XMLCAST, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("xmlconcat", XMLCONCAT, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("xmlelement", XMLELEMENT, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("xmlexists", XMLEXISTS, COL_NAME_KEYWORD, BARE_LABEL)
diff --git a/src/include/utils/xml.h b/src/include/utils/xml.h
index 0d7a816b9f..60bb464bd3 100644
--- a/src/include/utils/xml.h
+++ b/src/include/utils/xml.h
@@ -80,6 +80,7 @@ extern bool xml_is_document(xmltype *arg);
 extern text *xmltotext_with_options(xmltype *data, XmlOptionType xmloption_arg,
 									bool indent);
 extern char *escape_xml(const char *str);
+extern char *unescape_xml(const char *str);
 
 extern char *map_sql_identifier_to_xml_name(const char *ident, bool fully_escaped, bool escape_period);
 extern char *map_xml_name_to_sql_identifier(const char *name);
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 103a22a3b1..9ba5400d2b 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1881,3 +1881,568 @@ SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
  x&lt;P&gt;73&lt;/P&gt;0.42truej
 (1 row)
 
+-- for xmlcast() tests
+INSERT INTO xmltest
+ VALUES (42,
+'<?xml version="1.0" encoding="utf-8"?>
+ <xmlcast>
+  <period1>P1Y2M3DT4H5M6S</period1>
+  <period2>1 year 2 mons 3 days 4 hours 5 minutes 6 seconds</period2>
+  <date1>2002-09-24</date1>
+  <date2>2002-09-24+06:00</date2>
+  <time>09:30:10.5</time>
+  <time_tz1>09:30:10Z</time_tz1>
+  <time_tz2>09:30:10-06:00</time_tz2>
+  <time_tz3>09:30:10+06:00</time_tz3>
+  <timestamp1>2002-05-30T09:00:00</timestamp1>
+  <timestamp2>2002-05-30T09:30:10.5</timestamp2>
+  <timestamp_tz1>2002-05-30T09:30:10Z</timestamp_tz1>
+  <timestamp_tz2>2002-05-30T09:30:10-06:00</timestamp_tz2>
+  <timestamp_tz3>2002-05-30T09:30:10+06:00</timestamp_tz3>
+  <text1>foo bar</text1>
+  <text2>       foo bar     </text2>
+  <text3>foo &amp; &lt;&quot;bar&quot;&gt;</text3>
+  <decimal1>42.7312345678910</decimal1>
+  <decimal2>+42.7312345678910</decimal2>
+  <decimal3>-42.7312345678910</decimal3>
+  <integer1>42</integer1>
+  <integer2>+42</integer2>
+  <integer3>-42</integer3>
+  <long1>4273535420162021</long1>
+  <long2>+4273535420162021</long2>
+  <long3>-4273535420162021</long3>
+  <bool1 att="true">42</bool1>
+  <bool2 att="false">73</bool2>
+  <empty></empty>
+ </xmlcast>'::xml
+);
+-- This prevents the xmlcast regression tests from failing if the system's timezone has been changed.
+SET timezone TO 'America/Los_Angeles';
+-- xmlcast exceptions
+\set VERBOSITY terse
+SELECT xmlcast((xpath('//text1/text()', data))[1] AS text[]) FROM xmltest WHERE id = 42;
+ERROR:  cannot cast from 'xml' to 'text[]' at character 8
+SELECT xmlcast((xpath('//text1/integer1()', data))[1] AS int[]) FROM xmltest WHERE id = 42;
+ERROR:  cannot cast from 'xml' to 'integer[]' at character 8
+SELECT xmlcast(NULL AS text);
+ERROR:  cannot cast from 'unknown' to 'text' at character 8
+SELECT xmlcast('foo'::text AS varchar);
+ERROR:  cannot cast from 'text' to 'character varying' at character 8
+SELECT xmlcast(42 AS text);
+ERROR:  cannot cast from 'integer' to 'text' at character 8
+SELECT xmlcast(array['foo','bar'] AS xml);
+ERROR:  cannot cast from 'text[]' to 'xml' at character 8
+\set VERBOSITY default
+-- xmlcast tests for "XML to non-XML" expressions
+SELECT
+  xmlcast((xpath('//date1/text()', data))[1] AS date), pg_typeof(xmlcast((xpath('//date1/text()', data))[1] AS date)),
+  xmlcast((xpath('//date2/text()', data))[1] AS date), pg_typeof(xmlcast((xpath('//date2/text()', data))[1] AS date))
+FROM xmltest WHERE id = 42;
+  xmlcast   | pg_typeof |  xmlcast   | pg_typeof 
+------------+-----------+------------+-----------
+ 09-24-2002 | date      | 09-24-2002 | date
+(1 row)
+
+SELECT
+  xmlcast((xpath('//period1/text()', data))[1] AS interval), pg_typeof(xmlcast((xpath('//period1/text()', data))[1] AS interval)),
+  xmlcast((xpath('//period2/text()', data))[1] AS interval), pg_typeof(xmlcast((xpath('//period2/text()', data))[1] AS interval))
+FROM xmltest WHERE id = 42;
+                   xmlcast                    | pg_typeof |                   xmlcast                    | pg_typeof 
+----------------------------------------------+-----------+----------------------------------------------+-----------
+ @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs | interval  | @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs | interval
+(1 row)
+
+SELECT
+  xmlcast((xpath('//time/text()', data))[1] AS time), pg_typeof(xmlcast((xpath('//time/text()', data))[1] AS time)),
+  xmlcast((xpath('//time_tz1/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz1/text()', data))[1] AS time with time zone)),
+  xmlcast((xpath('//time_tz2/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz2/text()', data))[1] AS time with time zone)),
+  xmlcast((xpath('//time_tz3/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz3/text()', data))[1] AS time with time zone))
+FROM xmltest WHERE id = 42;
+  xmlcast   |       pg_typeof        |   xmlcast   |      pg_typeof      |   xmlcast   |      pg_typeof      |   xmlcast   |      pg_typeof      
+------------+------------------------+-------------+---------------------+-------------+---------------------+-------------+---------------------
+ 09:30:10.5 | time without time zone | 09:30:10+00 | time with time zone | 09:30:10-06 | time with time zone | 09:30:10+06 | time with time zone
+(1 row)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS text)),
+  xmlcast((xpath('//text2/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS text)),
+  xmlcast((xpath('//text3/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS text))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof |       xmlcast       | pg_typeof |    xmlcast    | pg_typeof 
+---------+-----------+---------------------+-----------+---------------+-----------
+ foo bar | text      |        foo bar      | text      | foo & <"bar"> | text
+(1 row)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS varchar)),
+  xmlcast((xpath('//text2/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS varchar)),
+  xmlcast((xpath('//text3/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS varchar))
+FROM xmltest WHERE id = 42;
+ xmlcast |     pg_typeof     |       xmlcast       |     pg_typeof     |    xmlcast    |     pg_typeof     
+---------+-------------------+---------------------+-------------------+---------------+-------------------
+ foo bar | character varying |        foo bar      | character varying | foo & <"bar"> | character varying
+(1 row)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS name)),
+  xmlcast((xpath('//text2/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS name)),
+  xmlcast((xpath('//text3/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS name))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof |       xmlcast       | pg_typeof |    xmlcast    | pg_typeof 
+---------+-----------+---------------------+-----------+---------------+-----------
+ foo bar | name      |        foo bar      | name      | foo & <"bar"> | name
+(1 row)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS bpchar)),
+  xmlcast((xpath('//text2/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS bpchar)),
+  xmlcast((xpath('//text3/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS bpchar))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof |       xmlcast       | pg_typeof |    xmlcast    | pg_typeof 
+---------+-----------+---------------------+-----------+---------------+-----------
+ foo bar | character |        foo bar      | character | foo & <"bar"> | character
+(1 row)
+
+SELECT
+  xmlcast((xpath('//decimal1/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal1/text()', data))[1] AS numeric)),
+  xmlcast((xpath('//decimal2/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal2/text()', data))[1] AS numeric)),
+  xmlcast((xpath('//decimal3/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal3/text()', data))[1] AS numeric))
+FROM xmltest WHERE id = 42;
+     xmlcast      | pg_typeof |     xmlcast      | pg_typeof |      xmlcast      | pg_typeof 
+------------------+-----------+------------------+-----------+-------------------+-----------
+ 42.7312345678910 | numeric   | 42.7312345678910 | numeric   | -42.7312345678910 | numeric
+(1 row)
+
+SELECT
+  xmlcast((xpath('//decimal1/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal1/text()', data))[1] AS double precision)),
+  xmlcast((xpath('//decimal2/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal2/text()', data))[1] AS double precision)),
+  xmlcast((xpath('//decimal3/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal3/text()', data))[1] AS double precision))
+FROM xmltest WHERE id = 42;
+     xmlcast     |    pg_typeof     |     xmlcast     |    pg_typeof     |     xmlcast      |    pg_typeof     
+-----------------+------------------+-----------------+------------------+------------------+------------------
+ 42.731234567891 | double precision | 42.731234567891 | double precision | -42.731234567891 | double precision
+(1 row)
+
+SELECT
+  xmlcast((xpath('//integer1/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer1/text()', data))[1] AS int)),
+  xmlcast((xpath('//integer2/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer2/text()', data))[1] AS int)),
+  xmlcast((xpath('//integer3/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer3/text()', data))[1] AS int))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+      42 | integer   |      42 | integer   |     -42 | integer
+(1 row)
+
+SELECT
+  xmlcast((xpath('//long1/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long1/text()', data))[1] AS bigint)),
+  xmlcast((xpath('//long2/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long2/text()', data))[1] AS bigint)),
+  xmlcast((xpath('//long3/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long3/text()', data))[1] AS bigint))
+FROM xmltest WHERE id = 42;
+     xmlcast      | pg_typeof |     xmlcast      | pg_typeof |      xmlcast      | pg_typeof 
+------------------+-----------+------------------+-----------+-------------------+-----------
+ 4273535420162021 | bigint    | 4273535420162021 | bigint    | -4273535420162021 | bigint
+(1 row)
+
+SELECT
+  xmlcast((xpath('//bool1/@att', data))[1] AS boolean), pg_typeof(xmlcast((xpath('//bool1/@att', data))[1] AS boolean)),
+  xmlcast((xpath('//bool2/@att', data))[1] AS boolean), pg_typeof(xmlcast((xpath('//bool1/@att', data))[1] AS boolean))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------
+ t       | boolean   | f       | boolean
+(1 row)
+
+SELECT xmlcast((xpath('//empty/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//empty/text()', data))[1] AS text))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof 
+---------+-----------
+         | text
+(1 row)
+
+-- xmlcast tests for "XML to XML" expressions
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS xml)),
+  xmlcast((xpath('//text2/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS xml)),
+  xmlcast((xpath('//text3/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS xml))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof |       xmlcast       | pg_typeof |         xmlcast         | pg_typeof 
+---------+-----------+---------------------+-----------+-------------------------+-----------
+ foo bar | xml       |        foo bar      | xml       | foo &amp; &lt;"bar"&gt; | xml
+(1 row)
+
+-- xmlcast tests for "non-XML to XML" expressions
+SELECT j, pg_typeof(j) FROM xmlcast(NULL AS xml) t(j);
+ j | pg_typeof 
+---+-----------
+   | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo' AS xml) t(j);
+  j  | pg_typeof 
+-----+-----------
+ foo | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(''::text AS xml) t(j);
+ j | pg_typeof 
+---+-----------
+   | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(NULL::text AS xml) t(j);
+ j | pg_typeof 
+---+-----------
+   | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(''::xml AS text) t(j);
+ j | pg_typeof 
+---+-----------
+   | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(NULL::xml AS text) t(j);
+ j | pg_typeof 
+---+-----------
+   | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::text AS xml) t(j);
+                 j                 | pg_typeof 
+-----------------------------------+-----------
+ foo &amp; &lt;&quot;bar&quot;&gt; | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::varchar AS xml) t(j);
+                 j                 | pg_typeof 
+-----------------------------------+-----------
+ foo &amp; &lt;&quot;bar&quot;&gt; | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::name AS xml) t(j);
+                 j                 | pg_typeof 
+-----------------------------------+-----------
+ foo &amp; &lt;&quot;bar&quot;&gt; | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(xmltext(E'foo & <"bar">\r') AS text) t(j);
+        j        | pg_typeof 
+-----------------+-----------
+ foo & <"bar">\r | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(xmlcast(E'foo & <"bar">\r' AS xml) AS text) t(j);
+        j        | pg_typeof 
+-----------------+-----------
+ foo & <"bar">\r | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(to_date('29/05/2024','dd/mm/yyyy') AS xml) t(j);
+     j      | pg_typeof 
+------------+-----------
+ 2024-05-29 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('2024-05-29 12:04:10.703585+02'::timestamp with time zone at time zone 'Europe/Berlin' AS xml) t(j);
+             j              | pg_typeof 
+----------------------------+-----------
+ 2024-05-29T12:04:10.703585 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('2024-05-29 12:04:10.703585+02'::timestamp without time zone AS xml) t(j);
+             j              | pg_typeof 
+----------------------------+-----------
+ 2024-05-29T12:04:10.703585 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval AS xml) t(j);
+       j        | pg_typeof 
+----------------+-----------
+ P1Y2M3DT4H5M6S | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(427353542 AS xml) t(j);
+     j     | pg_typeof 
+-----------+-----------
+ 427353542 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(4273535420162021 AS xml) t(j);
+        j         | pg_typeof 
+------------------+-----------
+ 4273535420162021 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(42.007312345678910 AS xml) t(j);
+         j          | pg_typeof 
+--------------------+-----------
+ 42.007312345678910 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(42.007312345678910::double precision AS xml) t(j);
+         j         | pg_typeof 
+-------------------+-----------
+ 42.00731234567891 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(true AS xml) t(j);
+  j   | pg_typeof 
+------+-----------
+ true | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(false AS xml) t(j);
+   j   | pg_typeof 
+-------+-----------
+ false | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(42 = 73 AS xml) t(j);
+   j   | pg_typeof 
+-------+-----------
+ false | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(42 <> 73 AS xml) t(j);
+  j   | pg_typeof 
+------+-----------
+ true | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('11:11:11.5'::time AS xml) t(j);
+     j      | pg_typeof 
+------------+-----------
+ 11:11:11.5 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('11:11:11.5'::time with time zone at time zone 'Europe/Berlin' AS xml) t(j);
+       j       | pg_typeof 
+---------------+-----------
+ 20:11:11.5+02 | xml
+(1 row)
+
+-- Convert an XML string to bytea and back to xml
+SELECT xmlcast(convert_from(xmlcast('&lt;&quot;foo&amp;bar&quot;&gt;'::xml AS bytea),'UTF8')::xml AS text);
+   xmlcast   
+-------------
+ <"foo&bar">
+(1 row)
+
+SET xmlbinary TO hex;
+SELECT xmlcast(E'\\xDEADBEEF'::bytea AS xml);
+ xmlcast  
+----------
+ DEADBEEF
+(1 row)
+
+SET xmlbinary TO base64;
+SELECT xmlcast(E'\\xDEADBEEF'::bytea AS xml);
+ xmlcast  
+----------
+ 3q2+7w==
+(1 row)
+
+-- The BY REF and BY VALUE clauses are accepted but ignored.
+-- This checks if the results are indeed the same as without the clauses.
+SELECT
+  xmlcast('foo' AS xml)::text = xmlcast('foo' AS xml BY REF)::text,
+  xmlcast('foo' AS xml)::text = xmlcast('foo' AS xml BY VALUE)::text,
+  xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text BY REF),
+  xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast(42 AS xml)::text = xmlcast(42 AS xml BY REF)::text,
+  xmlcast(42 AS xml)::text = xmlcast(42 AS xml BY VALUE)::text,
+  xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY REF),
+  xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast(42.73 AS xml)::text = xmlcast(42.73 AS xml BY REF)::text,
+  xmlcast(42.73 AS xml)::text = xmlcast(42.73 AS xml BY VALUE)::text,
+  xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS numeric BY REF),
+  xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS numeric BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('2024-08-14'::date AS xml)::text = xmlcast('2024-08-14'::date AS xml BY REF)::text,
+  xmlcast('2024-08-14'::date AS xml)::text = xmlcast('2024-08-14'::date AS xml BY VALUE)::text,
+  xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14'::xml AS date BY REF),
+  xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14'::xml AS date BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('12:30:45'::time without time zone AS xml)::text = xmlcast('12:30:45'::time without time zone AS xml BY REF)::text,
+  xmlcast('12:30:45'::time without time zone AS xml)::text = xmlcast('12:30:45'::time without time zone AS xml BY VALUE)::text,
+  xmlcast('12:30:45'::xml AS time without time zone) = xmlcast('12:30:45'::xml AS time without time zone BY REF),
+  xmlcast('12:30:45'::xml AS time without time zone) = xmlcast('12:30:45'::xml AS time without time zone BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('09:30:10+06:00'::time with time zone AS xml)::text = xmlcast('09:30:10+06:00'::time with time zone AS xml BY REF)::text,
+  xmlcast('09:30:10+06:00'::time with time zone AS xml)::text = xmlcast('09:30:10+06:00'::time with time zone AS xml BY VALUE)::text,
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) = xmlcast('09:30:10+06:00'::xml AS time with time zone BY REF),
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) = xmlcast('09:30:10+06:00'::xml AS time with time zone BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml)::text = xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml BY REF)::text,
+  xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml)::text = xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml BY VALUE)::text,
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) = xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone BY REF),
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) = xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('1 year 2 mons'::interval AS xml)::text = xmlcast('1 year 2 mons'::interval AS xml BY REF)::text,
+  xmlcast('1 year 2 mons'::interval AS xml)::text = xmlcast('1 year 2 mons'::interval AS xml BY VALUE)::text,
+  xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 year 2 mons'::xml AS interval BY REF),
+  xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 year 2 mons'::xml AS interval BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+CREATE VIEW view_xmlcast_to_xml AS
+SELECT
+  xmlcast(NULL AS xml) AS c1,
+  xmlcast('foo' AS xml) AS c2,
+  xmlcast(''::text AS xml) AS c3,
+  xmlcast(NULL::text AS xml) AS c4,
+  xmlcast(''::xml AS text) AS c5,
+  xmlcast(NULL::xml AS text) c6,
+  xmlcast('foo & <"bar">'::text AS xml) AS c7,
+  xmlcast('foo & <"bar">'::varchar AS xml) AS c8,
+  xmlcast('foo & <"bar">'::name AS xml) AS c9,
+  xmlcast(xmltext(E'foo & <"bar">\r') AS text) AS c10,
+  xmlcast(xmlcast(E'foo & <"bar">\r' AS xml) AS text) AS c11,
+  xmlcast(to_date('29/05/2024','dd/mm/yyyy') AS xml) AS c12,
+  xmlcast('2024-05-29 12:04:10.703585+02'::timestamp with time zone at time zone 'Europe/Berlin' AS xml) AS c13,
+  xmlcast('2024-05-29 12:04:10.703585+02'::timestamp without time zone AS xml) AS c14,
+  xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval AS xml) AS c15,
+  xmlcast(427353542 AS xml) AS c16,
+  xmlcast(4273535420162021 AS xml) AS c17,
+  xmlcast(42.007312345678910 AS xml) AS c18,
+  xmlcast(42.007312345678910::double precision AS xml) AS c19,
+  xmlcast(true AS xml) AS c20,
+  xmlcast(false AS xml) AS c21,
+  xmlcast(42 = 73 AS xml) AS c22,
+  xmlcast(42 <> 73 AS xml) AS c23,
+  xmlcast('11:11:11.5'::time AS xml) AS c24,
+  xmlcast('11:11:11.5'::time with time zone at time zone 'Europe/Berlin' AS xml) AS c25;
+\sv view_xmlcast_to_xml
+CREATE OR REPLACE VIEW public.view_xmlcast_to_xml AS
+ SELECT XMLCAST(NULL::text AS xml)::xml AS c1,
+    XMLCAST('foo'::text AS xml)::xml AS c2,
+    XMLCAST(''::text AS xml)::xml AS c3,
+    XMLCAST(NULL::text AS xml)::xml AS c4,
+    XMLCAST(''::xml AS text)::text AS c5,
+    XMLCAST(NULL::xml AS text)::text AS c6,
+    XMLCAST('foo & <"bar">'::text AS xml)::xml AS c7,
+    XMLCAST('foo & <"bar">'::character varying AS xml)::xml AS c8,
+    XMLCAST('foo & <"bar">'::name::text AS xml)::xml AS c9,
+    XMLCAST(xmltext('foo & <"bar">
'::text) AS text)::text AS c10,
+    XMLCAST(XMLCAST('foo & <"bar">
'::text AS xml)::xml AS text)::text AS c11,
+    XMLCAST(to_date('29/05/2024'::text, 'dd/mm/yyyy'::text) AS xml)::xml AS c12,
+    XMLCAST(('Wed May 29 03:04:10.703585 2024 PDT'::timestamp with time zone AT TIME ZONE 'Europe/Berlin'::text) AS xml)::xml AS c13,
+    XMLCAST('Wed May 29 12:04:10.703585 2024'::timestamp without time zone AS xml)::xml AS c14,
+    XMLCAST('@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs'::interval AS xml)::xml AS c15,
+    XMLCAST(427353542::text AS xml)::xml AS c16,
+    XMLCAST('4273535420162021'::bigint::text AS xml)::xml AS c17,
+    XMLCAST(42.007312345678910::text AS xml)::xml AS c18,
+    XMLCAST(42.007312345678910::double precision::text AS xml)::xml AS c19,
+    XMLCAST(true AS xml)::xml AS c20,
+    XMLCAST(false AS xml)::xml AS c21,
+    XMLCAST(42 = 73 AS xml)::xml AS c22,
+    XMLCAST(42 <> 73 AS xml)::xml AS c23,
+    XMLCAST('11:11:11.5'::time without time zone::text AS xml)::xml AS c24,
+    XMLCAST(('11:11:11.5-07'::time with time zone AT TIME ZONE 'Europe/Berlin'::text)::text AS xml)::xml AS c25
+SELECT * FROM view_xmlcast_to_xml;
+ c1 | c2  | c3 | c4 | c5 | c6 |                c7                 |                c8                 |                c9                 |       c10       |       c11       |    c12     |            c13             |            c14             |      c15       |    c16    |       c17        |        c18         |        c19        | c20  |  c21  |  c22  | c23  |    c24     |      c25      
+----+-----+----+----+----+----+-----------------------------------+-----------------------------------+-----------------------------------+-----------------+-----------------+------------+----------------------------+----------------------------+----------------+-----------+------------------+--------------------+-------------------+------+-------+-------+------+------------+---------------
+    | foo |    |    |    |    | foo &amp; &lt;&quot;bar&quot;&gt; | foo &amp; &lt;&quot;bar&quot;&gt; | foo &amp; &lt;&quot;bar&quot;&gt; | foo & <"bar">\r | foo & <"bar">\r | 2024-05-29 | 2024-05-29T12:04:10.703585 | 2024-05-29T12:04:10.703585 | P1Y2M3DT4H5M6S | 427353542 | 4273535420162021 | 42.007312345678910 | 42.00731234567891 | true | false | false | true | 11:11:11.5 | 20:11:11.5+02
+(1 row)
+
+CREATE VIEW view_xmlcast_from_xml AS
+SELECT
+  xmlcast('P1Y2M3DT4H5M6S'::xml AS interval) AS c1,
+  xmlcast('1 year 2 mons 3 days 4 hours 5 minutes 6 seconds'::xml AS interval) AS c2,
+  xmlcast('2002-09-24'::xml AS date) AS c3,
+  xmlcast('2002-09-24+06:00'::xml AS date) AS c4,
+  xmlcast('09:30:10Z'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c5,
+  xmlcast('09:30:10-06:00'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c6,
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c7,
+  xmlcast('2002-05-30T09:30:10Z'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c8,
+  xmlcast('2002-05-30T09:30:10-06:00'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c9,
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c10,
+  xmlcast('foo bar'::xml AS text) AS c11,
+  xmlcast('       foo bar     '::xml AS varchar) AS c12,
+  xmlcast('foo &amp; &lt;&quot;bar&quot;&gt;'::xml AS text) AS c13,
+  xmlcast('42.7312345678910'::xml AS numeric) AS c14,
+  xmlcast('+42.7312345678910'::xml AS numeric) AS c15,
+  xmlcast('-42.7312345678910'::xml AS numeric) AS c16,
+  xmlcast('42'::xml AS integer) AS c17,
+  xmlcast('+42'::xml AS integer) AS c18,
+  xmlcast('-42'::xml AS integer) AS c19,
+  xmlcast('4273535420162021'::xml AS bigint) AS c20,
+  xmlcast('+4273535420162021'::xml AS bigint) AS c21,
+  xmlcast('-4273535420162021'::xml AS bigint) AS c22,
+  xmlcast('true'::xml AS boolean) AS c23,
+  xmlcast('false'::xml AS boolean) AS c24,
+  xmlcast(''::xml AS character varying) AS c25,
+  xmlcast(NULL::xml AS character varying) AS c26;
+\sv view_xmlcast_from_xml
+CREATE OR REPLACE VIEW public.view_xmlcast_from_xml AS
+ SELECT XMLCAST('P1Y2M3DT4H5M6S'::xml AS interval)::interval AS c1,
+    XMLCAST('1 year 2 mons 3 days 4 hours 5 minutes 6 seconds'::xml AS interval)::interval AS c2,
+    XMLCAST('2002-09-24'::xml AS date)::date AS c3,
+    XMLCAST('2002-09-24+06:00'::xml AS date)::date AS c4,
+    (XMLCAST('09:30:10Z'::xml AS time with time zone)::time with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c5,
+    (XMLCAST('09:30:10-06:00'::xml AS time with time zone)::time with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c6,
+    (XMLCAST('09:30:10+06:00'::xml AS time with time zone)::time with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c7,
+    (XMLCAST('2002-05-30T09:30:10Z'::xml AS timestamp with time zone)::timestamp with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c8,
+    (XMLCAST('2002-05-30T09:30:10-06:00'::xml AS timestamp with time zone)::timestamp with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c9,
+    (XMLCAST('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone)::timestamp with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c10,
+    XMLCAST('foo bar'::xml AS text)::text AS c11,
+    XMLCAST('       foo bar     '::xml AS character varying)::character varying AS c12,
+    XMLCAST('foo &amp; &lt;&quot;bar&quot;&gt;'::xml AS text)::text AS c13,
+    XMLCAST('42.7312345678910'::xml AS numeric)::numeric AS c14,
+    XMLCAST('+42.7312345678910'::xml AS numeric)::numeric AS c15,
+    XMLCAST('-42.7312345678910'::xml AS numeric)::numeric AS c16,
+    XMLCAST('42'::xml AS integer)::integer AS c17,
+    XMLCAST('+42'::xml AS integer)::integer AS c18,
+    XMLCAST('-42'::xml AS integer)::integer AS c19,
+    XMLCAST('4273535420162021'::xml AS bigint)::bigint AS c20,
+    XMLCAST('+4273535420162021'::xml AS bigint)::bigint AS c21,
+    XMLCAST('-4273535420162021'::xml AS bigint)::bigint AS c22,
+    XMLCAST('true'::xml AS boolean)::boolean AS c23,
+    XMLCAST('false'::xml AS boolean)::boolean AS c24,
+    XMLCAST(''::xml AS character varying)::character varying AS c25,
+    XMLCAST(NULL::xml AS character varying)::character varying AS c26
+SELECT * FROM view_xmlcast_from_xml;
+                      c1                      |                      c2                      |     c3     |     c4     |     c5      |     c6      |     c7      |            c8            |            c9            |           c10            |   c11   |         c12         |      c13      |       c14        |       c15        |        c16        | c17 | c18 | c19 |       c20        |       c21        |        c22        | c23 | c24 | c25 | c26 
+----------------------------------------------+----------------------------------------------+------------+------------+-------------+-------------+-------------+--------------------------+--------------------------+--------------------------+---------+---------------------+---------------+------------------+------------------+-------------------+-----+-----+-----+------------------+------------------+-------------------+-----+-----+-----+-----
+ @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs | @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs | 09-24-2002 | 09-24-2002 | 11:30:10+02 | 17:30:10+02 | 05:30:10+02 | Thu May 30 11:30:10 2002 | Thu May 30 17:30:10 2002 | Thu May 30 05:30:10 2002 | foo bar |        foo bar      | foo & <"bar"> | 42.7312345678910 | 42.7312345678910 | -42.7312345678910 |  42 |  42 | -42 | 4273535420162021 | 4273535420162021 | -4273535420162021 | t   | f   |     | 
+(1 row)
+
diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index 73c411118a..40d642d59d 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -1496,3 +1496,432 @@ ERROR:  unsupported XML feature
 LINE 1: SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j':...
                              ^
 DETAIL:  This functionality requires the server to be built with libxml support.
+-- for xmlcast() tests
+INSERT INTO xmltest
+ VALUES (42,
+'<?xml version="1.0" encoding="utf-8"?>
+ <xmlcast>
+  <period1>P1Y2M3DT4H5M6S</period1>
+  <period2>1 year 2 mons 3 days 4 hours 5 minutes 6 seconds</period2>
+  <date1>2002-09-24</date1>
+  <date2>2002-09-24+06:00</date2>
+  <time>09:30:10.5</time>
+  <time_tz1>09:30:10Z</time_tz1>
+  <time_tz2>09:30:10-06:00</time_tz2>
+  <time_tz3>09:30:10+06:00</time_tz3>
+  <timestamp1>2002-05-30T09:00:00</timestamp1>
+  <timestamp2>2002-05-30T09:30:10.5</timestamp2>
+  <timestamp_tz1>2002-05-30T09:30:10Z</timestamp_tz1>
+  <timestamp_tz2>2002-05-30T09:30:10-06:00</timestamp_tz2>
+  <timestamp_tz3>2002-05-30T09:30:10+06:00</timestamp_tz3>
+  <text1>foo bar</text1>
+  <text2>       foo bar     </text2>
+  <text3>foo &amp; &lt;&quot;bar&quot;&gt;</text3>
+  <decimal1>42.7312345678910</decimal1>
+  <decimal2>+42.7312345678910</decimal2>
+  <decimal3>-42.7312345678910</decimal3>
+  <integer1>42</integer1>
+  <integer2>+42</integer2>
+  <integer3>-42</integer3>
+  <long1>4273535420162021</long1>
+  <long2>+4273535420162021</long2>
+  <long3>-4273535420162021</long3>
+  <bool1 att="true">42</bool1>
+  <bool2 att="false">73</bool2>
+  <empty></empty>
+ </xmlcast>'::xml
+);
+ERROR:  unsupported XML feature
+LINE 3: '<?xml version="1.0" encoding="utf-8"?>
+        ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+-- This prevents the xmlcast regression tests from failing if the system's timezone has been changed.
+SET timezone TO 'America/Los_Angeles';
+-- xmlcast exceptions
+\set VERBOSITY terse
+SELECT xmlcast((xpath('//text1/text()', data))[1] AS text[]) FROM xmltest WHERE id = 42;
+ERROR:  cannot cast from 'xml' to 'text[]' at character 8
+SELECT xmlcast((xpath('//text1/integer1()', data))[1] AS int[]) FROM xmltest WHERE id = 42;
+ERROR:  cannot cast from 'xml' to 'integer[]' at character 8
+SELECT xmlcast(NULL AS text);
+ERROR:  cannot cast from 'unknown' to 'text' at character 8
+SELECT xmlcast('foo'::text AS varchar);
+ERROR:  cannot cast from 'text' to 'character varying' at character 8
+SELECT xmlcast(42 AS text);
+ERROR:  cannot cast from 'integer' to 'text' at character 8
+SELECT xmlcast(array['foo','bar'] AS xml);
+ERROR:  cannot cast from 'text[]' to 'xml' at character 8
+\set VERBOSITY default
+-- xmlcast tests for "XML to non-XML" expressions
+SELECT
+  xmlcast((xpath('//date1/text()', data))[1] AS date), pg_typeof(xmlcast((xpath('//date1/text()', data))[1] AS date)),
+  xmlcast((xpath('//date2/text()', data))[1] AS date), pg_typeof(xmlcast((xpath('//date2/text()', data))[1] AS date))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//period1/text()', data))[1] AS interval), pg_typeof(xmlcast((xpath('//period1/text()', data))[1] AS interval)),
+  xmlcast((xpath('//period2/text()', data))[1] AS interval), pg_typeof(xmlcast((xpath('//period2/text()', data))[1] AS interval))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//time/text()', data))[1] AS time), pg_typeof(xmlcast((xpath('//time/text()', data))[1] AS time)),
+  xmlcast((xpath('//time_tz1/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz1/text()', data))[1] AS time with time zone)),
+  xmlcast((xpath('//time_tz2/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz2/text()', data))[1] AS time with time zone)),
+  xmlcast((xpath('//time_tz3/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz3/text()', data))[1] AS time with time zone))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS text)),
+  xmlcast((xpath('//text2/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS text)),
+  xmlcast((xpath('//text3/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS text))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS varchar)),
+  xmlcast((xpath('//text2/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS varchar)),
+  xmlcast((xpath('//text3/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS varchar))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS name)),
+  xmlcast((xpath('//text2/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS name)),
+  xmlcast((xpath('//text3/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS name))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS bpchar)),
+  xmlcast((xpath('//text2/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS bpchar)),
+  xmlcast((xpath('//text3/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS bpchar))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//decimal1/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal1/text()', data))[1] AS numeric)),
+  xmlcast((xpath('//decimal2/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal2/text()', data))[1] AS numeric)),
+  xmlcast((xpath('//decimal3/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal3/text()', data))[1] AS numeric))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//decimal1/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal1/text()', data))[1] AS double precision)),
+  xmlcast((xpath('//decimal2/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal2/text()', data))[1] AS double precision)),
+  xmlcast((xpath('//decimal3/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal3/text()', data))[1] AS double precision))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//integer1/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer1/text()', data))[1] AS int)),
+  xmlcast((xpath('//integer2/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer2/text()', data))[1] AS int)),
+  xmlcast((xpath('//integer3/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer3/text()', data))[1] AS int))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//long1/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long1/text()', data))[1] AS bigint)),
+  xmlcast((xpath('//long2/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long2/text()', data))[1] AS bigint)),
+  xmlcast((xpath('//long3/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long3/text()', data))[1] AS bigint))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT
+  xmlcast((xpath('//bool1/@att', data))[1] AS boolean), pg_typeof(xmlcast((xpath('//bool1/@att', data))[1] AS boolean)),
+  xmlcast((xpath('//bool2/@att', data))[1] AS boolean), pg_typeof(xmlcast((xpath('//bool1/@att', data))[1] AS boolean))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------
+(0 rows)
+
+SELECT xmlcast((xpath('//empty/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//empty/text()', data))[1] AS text))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof 
+---------+-----------
+(0 rows)
+
+-- xmlcast tests for "XML to XML" expressions
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS xml)),
+  xmlcast((xpath('//text2/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS xml)),
+  xmlcast((xpath('//text3/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS xml))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+(0 rows)
+
+-- xmlcast tests for "non-XML to XML" expressions
+SELECT j, pg_typeof(j) FROM xmlcast(NULL AS xml) t(j);
+ j | pg_typeof 
+---+-----------
+   | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo' AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(''::text AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(NULL::text AS xml) t(j);
+ j | pg_typeof 
+---+-----------
+   | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(''::xml AS text) t(j);
+ERROR:  unsupported XML feature
+LINE 1: SELECT j, pg_typeof(j) FROM xmlcast(''::xml AS text) t(j);
+                                            ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(NULL::xml AS text) t(j);
+ j | pg_typeof 
+---+-----------
+   | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::text AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::varchar AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::name AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(xmltext(E'foo & <"bar">\r') AS text) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(xmlcast(E'foo & <"bar">\r' AS xml) AS text) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(to_date('29/05/2024','dd/mm/yyyy') AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast('2024-05-29 12:04:10.703585+02'::timestamp with time zone at time zone 'Europe/Berlin' AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast('2024-05-29 12:04:10.703585+02'::timestamp without time zone AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(427353542 AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(4273535420162021 AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(42.007312345678910 AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(42.007312345678910::double precision AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(true AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(false AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(42 = 73 AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast(42 <> 73 AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast('11:11:11.5'::time AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT j, pg_typeof(j) FROM xmlcast('11:11:11.5'::time with time zone at time zone 'Europe/Berlin' AS xml) t(j);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+-- Convert an XML string to bytea and back to xml
+SELECT xmlcast(convert_from(xmlcast('&lt;&quot;foo&amp;bar&quot;&gt;'::xml AS bytea),'UTF8')::xml AS text);
+ERROR:  unsupported XML feature
+LINE 1: SELECT xmlcast(convert_from(xmlcast('&lt;&quot;foo&amp;bar&q...
+                                            ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SET xmlbinary TO hex;
+SELECT xmlcast(E'\\xDEADBEEF'::bytea AS xml);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SET xmlbinary TO base64;
+SELECT xmlcast(E'\\xDEADBEEF'::bytea AS xml);
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+-- The BY REF and BY VALUE clauses are accepted but ignored.
+-- This checks if the results are indeed the same as without the clauses.
+SELECT
+  xmlcast('foo' AS xml)::text = xmlcast('foo' AS xml BY REF)::text,
+  xmlcast('foo' AS xml)::text = xmlcast('foo' AS xml BY VALUE)::text,
+  xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text BY REF),
+  xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text BY VALUE);
+ERROR:  unsupported XML feature
+LINE 4:   xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text B...
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT
+  xmlcast(42 AS xml)::text = xmlcast(42 AS xml BY REF)::text,
+  xmlcast(42 AS xml)::text = xmlcast(42 AS xml BY VALUE)::text,
+  xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY REF),
+  xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY VALUE);
+ERROR:  unsupported XML feature
+LINE 4:   xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY RE...
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT
+  xmlcast(42.73 AS xml)::text = xmlcast(42.73 AS xml BY REF)::text,
+  xmlcast(42.73 AS xml)::text = xmlcast(42.73 AS xml BY VALUE)::text,
+  xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS numeric BY REF),
+  xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS numeric BY VALUE);
+ERROR:  unsupported XML feature
+LINE 4:   xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS...
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT
+  xmlcast('2024-08-14'::date AS xml)::text = xmlcast('2024-08-14'::date AS xml BY REF)::text,
+  xmlcast('2024-08-14'::date AS xml)::text = xmlcast('2024-08-14'::date AS xml BY VALUE)::text,
+  xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14'::xml AS date BY REF),
+  xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14'::xml AS date BY VALUE);
+ERROR:  unsupported XML feature
+LINE 4:   xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14':...
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT
+  xmlcast('12:30:45'::time without time zone AS xml)::text = xmlcast('12:30:45'::time without time zone AS xml BY REF)::text,
+  xmlcast('12:30:45'::time without time zone AS xml)::text = xmlcast('12:30:45'::time without time zone AS xml BY VALUE)::text,
+  xmlcast('12:30:45'::xml AS time without time zone) = xmlcast('12:30:45'::xml AS time without time zone BY REF),
+  xmlcast('12:30:45'::xml AS time without time zone) = xmlcast('12:30:45'::xml AS time without time zone BY VALUE);
+ERROR:  unsupported XML feature
+LINE 4:   xmlcast('12:30:45'::xml AS time without time zone) = xmlca...
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT
+  xmlcast('09:30:10+06:00'::time with time zone AS xml)::text = xmlcast('09:30:10+06:00'::time with time zone AS xml BY REF)::text,
+  xmlcast('09:30:10+06:00'::time with time zone AS xml)::text = xmlcast('09:30:10+06:00'::time with time zone AS xml BY VALUE)::text,
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) = xmlcast('09:30:10+06:00'::xml AS time with time zone BY REF),
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) = xmlcast('09:30:10+06:00'::xml AS time with time zone BY VALUE);
+ERROR:  unsupported XML feature
+LINE 4:   xmlcast('09:30:10+06:00'::xml AS time with time zone) = xm...
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT
+  xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml)::text = xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml BY REF)::text,
+  xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml)::text = xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml BY VALUE)::text,
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) = xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone BY REF),
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) = xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone BY VALUE);
+ERROR:  unsupported XML feature
+LINE 4:   xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with...
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT
+  xmlcast('1 year 2 mons'::interval AS xml)::text = xmlcast('1 year 2 mons'::interval AS xml BY REF)::text,
+  xmlcast('1 year 2 mons'::interval AS xml)::text = xmlcast('1 year 2 mons'::interval AS xml BY VALUE)::text,
+  xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 year 2 mons'::xml AS interval BY REF),
+  xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 year 2 mons'::xml AS interval BY VALUE);
+ERROR:  unsupported XML feature
+LINE 4:   xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 yea...
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+CREATE VIEW view_xmlcast_to_xml AS
+SELECT
+  xmlcast(NULL AS xml) AS c1,
+  xmlcast('foo' AS xml) AS c2,
+  xmlcast(''::text AS xml) AS c3,
+  xmlcast(NULL::text AS xml) AS c4,
+  xmlcast(''::xml AS text) AS c5,
+  xmlcast(NULL::xml AS text) c6,
+  xmlcast('foo & <"bar">'::text AS xml) AS c7,
+  xmlcast('foo & <"bar">'::varchar AS xml) AS c8,
+  xmlcast('foo & <"bar">'::name AS xml) AS c9,
+  xmlcast(xmltext(E'foo & <"bar">\r') AS text) AS c10,
+  xmlcast(xmlcast(E'foo & <"bar">\r' AS xml) AS text) AS c11,
+  xmlcast(to_date('29/05/2024','dd/mm/yyyy') AS xml) AS c12,
+  xmlcast('2024-05-29 12:04:10.703585+02'::timestamp with time zone at time zone 'Europe/Berlin' AS xml) AS c13,
+  xmlcast('2024-05-29 12:04:10.703585+02'::timestamp without time zone AS xml) AS c14,
+  xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval AS xml) AS c15,
+  xmlcast(427353542 AS xml) AS c16,
+  xmlcast(4273535420162021 AS xml) AS c17,
+  xmlcast(42.007312345678910 AS xml) AS c18,
+  xmlcast(42.007312345678910::double precision AS xml) AS c19,
+  xmlcast(true AS xml) AS c20,
+  xmlcast(false AS xml) AS c21,
+  xmlcast(42 = 73 AS xml) AS c22,
+  xmlcast(42 <> 73 AS xml) AS c23,
+  xmlcast('11:11:11.5'::time AS xml) AS c24,
+  xmlcast('11:11:11.5'::time with time zone at time zone 'Europe/Berlin' AS xml) AS c25;
+ERROR:  unsupported XML feature
+LINE 7:   xmlcast(''::xml AS text) AS c5,
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+\sv view_xmlcast_to_xml
+ERROR:  relation "view_xmlcast_to_xml" does not exist
+SELECT * FROM view_xmlcast_to_xml;
+ERROR:  relation "view_xmlcast_to_xml" does not exist
+LINE 1: SELECT * FROM view_xmlcast_to_xml;
+                      ^
+CREATE VIEW view_xmlcast_from_xml AS
+SELECT
+  xmlcast('P1Y2M3DT4H5M6S'::xml AS interval) AS c1,
+  xmlcast('1 year 2 mons 3 days 4 hours 5 minutes 6 seconds'::xml AS interval) AS c2,
+  xmlcast('2002-09-24'::xml AS date) AS c3,
+  xmlcast('2002-09-24+06:00'::xml AS date) AS c4,
+  xmlcast('09:30:10Z'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c5,
+  xmlcast('09:30:10-06:00'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c6,
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c7,
+  xmlcast('2002-05-30T09:30:10Z'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c8,
+  xmlcast('2002-05-30T09:30:10-06:00'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c9,
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c10,
+  xmlcast('foo bar'::xml AS text) AS c11,
+  xmlcast('       foo bar     '::xml AS varchar) AS c12,
+  xmlcast('foo &amp; &lt;&quot;bar&quot;&gt;'::xml AS text) AS c13,
+  xmlcast('42.7312345678910'::xml AS numeric) AS c14,
+  xmlcast('+42.7312345678910'::xml AS numeric) AS c15,
+  xmlcast('-42.7312345678910'::xml AS numeric) AS c16,
+  xmlcast('42'::xml AS integer) AS c17,
+  xmlcast('+42'::xml AS integer) AS c18,
+  xmlcast('-42'::xml AS integer) AS c19,
+  xmlcast('4273535420162021'::xml AS bigint) AS c20,
+  xmlcast('+4273535420162021'::xml AS bigint) AS c21,
+  xmlcast('-4273535420162021'::xml AS bigint) AS c22,
+  xmlcast('true'::xml AS boolean) AS c23,
+  xmlcast('false'::xml AS boolean) AS c24,
+  xmlcast(''::xml AS character varying) AS c25,
+  xmlcast(NULL::xml AS character varying) AS c26;
+ERROR:  unsupported XML feature
+LINE 3:   xmlcast('P1Y2M3DT4H5M6S'::xml AS interval) AS c1,
+                  ^
+DETAIL:  This functionality requires the server to be built with libxml support.
+\sv view_xmlcast_from_xml
+ERROR:  relation "view_xmlcast_from_xml" does not exist
+SELECT * FROM view_xmlcast_from_xml;
+ERROR:  relation "view_xmlcast_from_xml" does not exist
+LINE 1: SELECT * FROM view_xmlcast_from_xml;
+                      ^
diff --git a/src/test/regress/expected/xml_2.out b/src/test/regress/expected/xml_2.out
index a85d95358d..520329fbcb 100644
--- a/src/test/regress/expected/xml_2.out
+++ b/src/test/regress/expected/xml_2.out
@@ -1867,3 +1867,570 @@ SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
  x&lt;P&gt;73&lt;/P&gt;0.42truej
 (1 row)
 
+-- for xmlcast() tests
+INSERT INTO xmltest
+ VALUES (42,
+'<?xml version="1.0" encoding="utf-8"?>
+ <xmlcast>
+  <period1>P1Y2M3DT4H5M6S</period1>
+  <period2>1 year 2 mons 3 days 4 hours 5 minutes 6 seconds</period2>
+  <date1>2002-09-24</date1>
+  <date2>2002-09-24+06:00</date2>
+  <time>09:30:10.5</time>
+  <time_tz1>09:30:10Z</time_tz1>
+  <time_tz2>09:30:10-06:00</time_tz2>
+  <time_tz3>09:30:10+06:00</time_tz3>
+  <timestamp1>2002-05-30T09:00:00</timestamp1>
+  <timestamp2>2002-05-30T09:30:10.5</timestamp2>
+  <timestamp_tz1>2002-05-30T09:30:10Z</timestamp_tz1>
+  <timestamp_tz2>2002-05-30T09:30:10-06:00</timestamp_tz2>
+  <timestamp_tz3>2002-05-30T09:30:10+06:00</timestamp_tz3>
+  <text1>foo bar</text1>
+  <text2>       foo bar     </text2>
+  <text3>foo &amp; &lt;&quot;bar&quot;&gt;</text3>
+  <decimal1>42.7312345678910</decimal1>
+  <decimal2>+42.7312345678910</decimal2>
+  <decimal3>-42.7312345678910</decimal3>
+  <integer1>42</integer1>
+  <integer2>+42</integer2>
+  <integer3>-42</integer3>
+  <long1>4273535420162021</long1>
+  <long2>+4273535420162021</long2>
+  <long3>-4273535420162021</long3>
+  <bool1 att="true">42</bool1>
+  <bool2 att="false">73</bool2>
+  <empty></empty>
+ </xmlcast>'::xml
+);
+-- This prevents the xmlcast regression tests from failing if the system's timezone has been changed.
+SET timezone TO 'America/Los_Angeles';
+-- xmlcast exceptions
+\set VERBOSITY terse
+SELECT xmlcast((xpath('//text1/text()', data))[1] AS text[]) FROM xmltest WHERE id = 42;
+ERROR:  cannot cast from 'xml' to 'text[]' at character 8
+SELECT xmlcast((xpath('//text1/integer1()', data))[1] AS int[]) FROM xmltest WHERE id = 42;
+ERROR:  cannot cast from 'xml' to 'integer[]' at character 8
+SELECT xmlcast(NULL AS text);
+ERROR:  cannot cast from 'unknown' to 'text' at character 8
+SELECT xmlcast('foo'::text AS varchar);
+ERROR:  cannot cast from 'text' to 'character varying' at character 8
+SELECT xmlcast(42 AS text);
+ERROR:  cannot cast from 'integer' to 'text' at character 8
+SELECT xmlcast(array['foo','bar'] AS xml);
+ERROR:  cannot cast from 'text[]' to 'xml' at character 8
+\set VERBOSITY default
+-- xmlcast tests for "XML to non-XML" expressions
+SELECT
+  xmlcast((xpath('//date1/text()', data))[1] AS date), pg_typeof(xmlcast((xpath('//date1/text()', data))[1] AS date)),
+  xmlcast((xpath('//date2/text()', data))[1] AS date), pg_typeof(xmlcast((xpath('//date2/text()', data))[1] AS date))
+FROM xmltest WHERE id = 42;
+  xmlcast   | pg_typeof |  xmlcast   | pg_typeof 
+------------+-----------+------------+-----------
+ 09-24-2002 | date      | 09-24-2002 | date
+(1 row)
+
+SELECT
+  xmlcast((xpath('//period1/text()', data))[1] AS interval), pg_typeof(xmlcast((xpath('//period1/text()', data))[1] AS interval)),
+  xmlcast((xpath('//period2/text()', data))[1] AS interval), pg_typeof(xmlcast((xpath('//period2/text()', data))[1] AS interval))
+FROM xmltest WHERE id = 42;
+                   xmlcast                    | pg_typeof |                   xmlcast                    | pg_typeof 
+----------------------------------------------+-----------+----------------------------------------------+-----------
+ @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs | interval  | @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs | interval
+(1 row)
+
+SELECT
+  xmlcast((xpath('//time/text()', data))[1] AS time), pg_typeof(xmlcast((xpath('//time/text()', data))[1] AS time)),
+  xmlcast((xpath('//time_tz1/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz1/text()', data))[1] AS time with time zone)),
+  xmlcast((xpath('//time_tz2/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz2/text()', data))[1] AS time with time zone)),
+  xmlcast((xpath('//time_tz3/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz3/text()', data))[1] AS time with time zone))
+FROM xmltest WHERE id = 42;
+  xmlcast   |       pg_typeof        |   xmlcast   |      pg_typeof      |   xmlcast   |      pg_typeof      |   xmlcast   |      pg_typeof      
+------------+------------------------+-------------+---------------------+-------------+---------------------+-------------+---------------------
+ 09:30:10.5 | time without time zone | 09:30:10+00 | time with time zone | 09:30:10-06 | time with time zone | 09:30:10+06 | time with time zone
+(1 row)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS text)),
+  xmlcast((xpath('//text2/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS text)),
+  xmlcast((xpath('//text3/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS text))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof |       xmlcast       | pg_typeof |    xmlcast    | pg_typeof 
+---------+-----------+---------------------+-----------+---------------+-----------
+ foo bar | text      |        foo bar      | text      | foo & <"bar"> | text
+(1 row)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS varchar)),
+  xmlcast((xpath('//text2/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS varchar)),
+  xmlcast((xpath('//text3/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS varchar))
+FROM xmltest WHERE id = 42;
+ xmlcast |     pg_typeof     |       xmlcast       |     pg_typeof     |    xmlcast    |     pg_typeof     
+---------+-------------------+---------------------+-------------------+---------------+-------------------
+ foo bar | character varying |        foo bar      | character varying | foo & <"bar"> | character varying
+(1 row)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS name)),
+  xmlcast((xpath('//text2/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS name)),
+  xmlcast((xpath('//text3/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS name))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof |       xmlcast       | pg_typeof |    xmlcast    | pg_typeof 
+---------+-----------+---------------------+-----------+---------------+-----------
+ foo bar | name      |        foo bar      | name      | foo & <"bar"> | name
+(1 row)
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS bpchar)),
+  xmlcast((xpath('//text2/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS bpchar)),
+  xmlcast((xpath('//text3/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS bpchar))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof |       xmlcast       | pg_typeof |    xmlcast    | pg_typeof 
+---------+-----------+---------------------+-----------+---------------+-----------
+ foo bar | character |        foo bar      | character | foo & <"bar"> | character
+(1 row)
+
+SELECT
+  xmlcast((xpath('//decimal1/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal1/text()', data))[1] AS numeric)),
+  xmlcast((xpath('//decimal2/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal2/text()', data))[1] AS numeric)),
+  xmlcast((xpath('//decimal3/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal3/text()', data))[1] AS numeric))
+FROM xmltest WHERE id = 42;
+     xmlcast      | pg_typeof |     xmlcast      | pg_typeof |      xmlcast      | pg_typeof 
+------------------+-----------+------------------+-----------+-------------------+-----------
+ 42.7312345678910 | numeric   | 42.7312345678910 | numeric   | -42.7312345678910 | numeric
+(1 row)
+
+SELECT
+  xmlcast((xpath('//decimal1/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal1/text()', data))[1] AS double precision)),
+  xmlcast((xpath('//decimal2/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal2/text()', data))[1] AS double precision)),
+  xmlcast((xpath('//decimal3/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal3/text()', data))[1] AS double precision))
+FROM xmltest WHERE id = 42;
+     xmlcast     |    pg_typeof     |     xmlcast     |    pg_typeof     |     xmlcast      |    pg_typeof     
+-----------------+------------------+-----------------+------------------+------------------+------------------
+ 42.731234567891 | double precision | 42.731234567891 | double precision | -42.731234567891 | double precision
+(1 row)
+
+SELECT
+  xmlcast((xpath('//integer1/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer1/text()', data))[1] AS int)),
+  xmlcast((xpath('//integer2/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer2/text()', data))[1] AS int)),
+  xmlcast((xpath('//integer3/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer3/text()', data))[1] AS int))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------+---------+-----------
+      42 | integer   |      42 | integer   |     -42 | integer
+(1 row)
+
+SELECT
+  xmlcast((xpath('//long1/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long1/text()', data))[1] AS bigint)),
+  xmlcast((xpath('//long2/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long2/text()', data))[1] AS bigint)),
+  xmlcast((xpath('//long3/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long3/text()', data))[1] AS bigint))
+FROM xmltest WHERE id = 42;
+     xmlcast      | pg_typeof |     xmlcast      | pg_typeof |      xmlcast      | pg_typeof 
+------------------+-----------+------------------+-----------+-------------------+-----------
+ 4273535420162021 | bigint    | 4273535420162021 | bigint    | -4273535420162021 | bigint
+(1 row)
+
+SELECT
+  xmlcast((xpath('//bool1/@att', data))[1] AS boolean), pg_typeof(xmlcast((xpath('//bool1/@att', data))[1] AS boolean)),
+  xmlcast((xpath('//bool2/@att', data))[1] AS boolean), pg_typeof(xmlcast((xpath('//bool1/@att', data))[1] AS boolean))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof | xmlcast | pg_typeof 
+---------+-----------+---------+-----------
+ t       | boolean   | f       | boolean
+(1 row)
+
+SELECT xmlcast((xpath('//empty/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//empty/text()', data))[1] AS text))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof 
+---------+-----------
+         | text
+(1 row)
+
+-- xmlcast tests for "XML to XML" expressions
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS xml)),
+  xmlcast((xpath('//text2/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS xml)),
+  xmlcast((xpath('//text3/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS xml))
+FROM xmltest WHERE id = 42;
+ xmlcast | pg_typeof |       xmlcast       | pg_typeof |         xmlcast         | pg_typeof 
+---------+-----------+---------------------+-----------+-------------------------+-----------
+ foo bar | xml       |        foo bar      | xml       | foo &amp; &lt;"bar"&gt; | xml
+(1 row)
+
+-- xmlcast tests for "non-XML to XML" expressions
+SELECT j, pg_typeof(j) FROM xmlcast(NULL AS xml) t(j);
+ j | pg_typeof 
+---+-----------
+   | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo' AS xml) t(j);
+  j  | pg_typeof 
+-----+-----------
+ foo | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(''::text AS xml) t(j);
+ j | pg_typeof 
+---+-----------
+   | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(NULL::text AS xml) t(j);
+ j | pg_typeof 
+---+-----------
+   | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(''::xml AS text) t(j);
+ j | pg_typeof 
+---+-----------
+   | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(NULL::xml AS text) t(j);
+ j | pg_typeof 
+---+-----------
+   | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::text AS xml) t(j);
+                 j                 | pg_typeof 
+-----------------------------------+-----------
+ foo &amp; &lt;&quot;bar&quot;&gt; | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::varchar AS xml) t(j);
+                 j                 | pg_typeof 
+-----------------------------------+-----------
+ foo &amp; &lt;&quot;bar&quot;&gt; | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::name AS xml) t(j);
+                 j                 | pg_typeof 
+-----------------------------------+-----------
+ foo &amp; &lt;&quot;bar&quot;&gt; | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(xmltext(E'foo & <"bar">\r') AS text) t(j);
+        j        | pg_typeof 
+-----------------+-----------
+ foo & <"bar">\r | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(xmlcast(E'foo & <"bar">\r' AS xml) AS text) t(j);
+        j        | pg_typeof 
+-----------------+-----------
+ foo & <"bar">\r | text
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(to_date('29/05/2024','dd/mm/yyyy') AS xml) t(j);
+     j      | pg_typeof 
+------------+-----------
+ 2024-05-29 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('2024-05-29 12:04:10.703585+02'::timestamp with time zone at time zone 'Europe/Berlin' AS xml) t(j);
+             j              | pg_typeof 
+----------------------------+-----------
+ 2024-05-29T12:04:10.703585 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('2024-05-29 12:04:10.703585+02'::timestamp without time zone AS xml) t(j);
+             j              | pg_typeof 
+----------------------------+-----------
+ 2024-05-29T12:04:10.703585 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval AS xml) t(j);
+       j        | pg_typeof 
+----------------+-----------
+ P1Y2M3DT4H5M6S | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(427353542 AS xml) t(j);
+     j     | pg_typeof 
+-----------+-----------
+ 427353542 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(4273535420162021 AS xml) t(j);
+        j         | pg_typeof 
+------------------+-----------
+ 4273535420162021 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(42.007312345678910 AS xml) t(j);
+         j          | pg_typeof 
+--------------------+-----------
+ 42.007312345678910 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(42.007312345678910::double precision AS xml) t(j);
+         j         | pg_typeof 
+-------------------+-----------
+ 42.00731234567891 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(true AS xml) t(j);
+  j   | pg_typeof 
+------+-----------
+ true | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(false AS xml) t(j);
+   j   | pg_typeof 
+-------+-----------
+ false | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(42 = 73 AS xml) t(j);
+   j   | pg_typeof 
+-------+-----------
+ false | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast(42 <> 73 AS xml) t(j);
+  j   | pg_typeof 
+------+-----------
+ true | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('11:11:11.5'::time AS xml) t(j);
+     j      | pg_typeof 
+------------+-----------
+ 11:11:11.5 | xml
+(1 row)
+
+SELECT j, pg_typeof(j) FROM xmlcast('11:11:11.5'::time with time zone at time zone 'Europe/Berlin' AS xml) t(j);
+       j       | pg_typeof 
+---------------+-----------
+ 20:11:11.5+02 | xml
+(1 row)
+
+-- Convert an XML string to bytea and back to xml
+SELECT xmlcast(convert_from(xmlcast('&lt;&quot;foo&amp;bar&quot;&gt;'::xml AS bytea),'UTF8')::xml AS text);
+   xmlcast   
+-------------
+ <"foo&bar">
+(1 row)
+
+SET xmlbinary TO hex;
+SELECT xmlcast(E'\\xDEADBEEF'::bytea AS xml);
+ xmlcast  
+----------
+ DEADBEEF
+(1 row)
+
+SET xmlbinary TO base64;
+SELECT xmlcast(E'\\xDEADBEEF'::bytea AS xml);
+ xmlcast  
+----------
+ 3q2+7w==
+(1 row)
+
+-- The BY REF and BY VALUE clauses are accepted but ignored.
+-- This checks if the results are indeed the same as without the clauses.
+SELECT
+  xmlcast('foo' AS xml)::text = xmlcast('foo' AS xml BY REF)::text,
+  xmlcast('foo' AS xml)::text = xmlcast('foo' AS xml BY VALUE)::text,
+  xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text BY REF),
+  xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast(42 AS xml)::text = xmlcast(42 AS xml BY REF)::text,
+  xmlcast(42 AS xml)::text = xmlcast(42 AS xml BY VALUE)::text,
+  xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY REF),
+  xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast(42.73 AS xml)::text = xmlcast(42.73 AS xml BY REF)::text,
+  xmlcast(42.73 AS xml)::text = xmlcast(42.73 AS xml BY VALUE)::text,
+  xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS numeric BY REF),
+  xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS numeric BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('2024-08-14'::date AS xml)::text = xmlcast('2024-08-14'::date AS xml BY REF)::text,
+  xmlcast('2024-08-14'::date AS xml)::text = xmlcast('2024-08-14'::date AS xml BY VALUE)::text,
+  xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14'::xml AS date BY REF),
+  xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14'::xml AS date BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('12:30:45'::time without time zone AS xml)::text = xmlcast('12:30:45'::time without time zone AS xml BY REF)::text,
+  xmlcast('12:30:45'::time without time zone AS xml)::text = xmlcast('12:30:45'::time without time zone AS xml BY VALUE)::text,
+  xmlcast('12:30:45'::xml AS time without time zone) = xmlcast('12:30:45'::xml AS time without time zone BY REF),
+  xmlcast('12:30:45'::xml AS time without time zone) = xmlcast('12:30:45'::xml AS time without time zone BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('09:30:10+06:00'::time with time zone AS xml)::text = xmlcast('09:30:10+06:00'::time with time zone AS xml BY REF)::text,
+  xmlcast('09:30:10+06:00'::time with time zone AS xml)::text = xmlcast('09:30:10+06:00'::time with time zone AS xml BY VALUE)::text,
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) = xmlcast('09:30:10+06:00'::xml AS time with time zone BY REF),
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) = xmlcast('09:30:10+06:00'::xml AS time with time zone BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml)::text = xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml BY REF)::text,
+  xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml)::text = xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml BY VALUE)::text,
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) = xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone BY REF),
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) = xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT
+  xmlcast('1 year 2 mons'::interval AS xml)::text = xmlcast('1 year 2 mons'::interval AS xml BY REF)::text,
+  xmlcast('1 year 2 mons'::interval AS xml)::text = xmlcast('1 year 2 mons'::interval AS xml BY VALUE)::text,
+  xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 year 2 mons'::xml AS interval BY REF),
+  xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 year 2 mons'::xml AS interval BY VALUE);
+ ?column? | ?column? | ?column? | ?column? 
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+CREATE VIEW view_xmlcast_to_xml AS
+SELECT
+  xmlcast(NULL AS xml) AS c1,
+  xmlcast('foo' AS xml) AS c2,
+  xmlcast(''::text AS xml) AS c3,
+  xmlcast(NULL::text AS xml) AS c4,
+  xmlcast(''::xml AS text) AS c5,
+  xmlcast(NULL::xml AS text) c6,
+  xmlcast('foo & <"bar">'::text AS xml) AS c7,
+  xmlcast('foo & <"bar">'::varchar AS xml) AS c8,
+  xmlcast('foo & <"bar">'::name AS xml) AS c9,
+  xmlcast(xmltext(E'foo & <"bar">\r') AS text) AS c10,
+  xmlcast(xmlcast(E'foo & <"bar">\r' AS xml) AS text) AS c11,
+  xmlcast(to_date('29/05/2024','dd/mm/yyyy') AS xml) AS c12,
+  xmlcast('2024-05-29 12:04:10.703585+02'::timestamp with time zone at time zone 'Europe/Berlin' AS xml) AS c13,
+  xmlcast('2024-05-29 12:04:10.703585+02'::timestamp without time zone AS xml) AS c14,
+  xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval AS xml) AS c15,
+  xmlcast(427353542 AS xml) AS c16,
+  xmlcast(4273535420162021 AS xml) AS c17,
+  xmlcast(42.007312345678910 AS xml) AS c18,
+  xmlcast(42.007312345678910::double precision AS xml) AS c19,
+  xmlcast(true AS xml) AS c20,
+  xmlcast(false AS xml) AS c21,
+  xmlcast(42 = 73 AS xml) AS c22,
+  xmlcast(42 <> 73 AS xml) AS c23,
+  xmlcast('11:11:11.5'::time AS xml) AS c24,
+  xmlcast('11:11:11.5'::time with time zone at time zone 'Europe/Berlin' AS xml) AS c25;
+\sv view_xmlcast_to_xml
+CREATE OR REPLACE VIEW public.view_xmlcast_to_xml AS
+ SELECT XMLCAST(NULL::text AS xml)::xml AS c1,
+    XMLCAST('foo'::text AS xml)::xml AS c2,
+    XMLCAST(''::text AS xml)::xml AS c3,
+    XMLCAST(NULL::text AS xml)::xml AS c4,
+    XMLCAST(''::xml AS text)::text AS c5,
+    XMLCAST(NULL::xml AS text)::text AS c6,
+    XMLCAST('foo & <"bar">'::text AS xml)::xml AS c7,
+    XMLCAST('foo & <"bar">'::character varying AS xml)::xml AS c8,
+    XMLCAST('foo & <"bar">'::name::text AS xml)::xml AS c9,
+    XMLCAST(xmltext('foo & <"bar">
+'::text) AS text)::text AS c10,
+    XMLCAST(XMLCAST('foo & <"bar">
+'::text AS xml)::xml AS text)::text AS c11,
+    XMLCAST(to_date('29/05/2024'::text, 'dd/mm/yyyy'::text) AS xml)::xml AS c12,
+    XMLCAST(('Wed May 29 03:04:10.703585 2024 PDT'::timestamp with time zone AT TIME ZONE 'Europe/Berlin'::text) AS xml)::xml AS c13,
+    XMLCAST('Wed May 29 12:04:10.703585 2024'::timestamp without time zone AS xml)::xml AS c14,
+    XMLCAST('@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs'::interval AS xml)::xml AS c15,
+    XMLCAST(427353542::text AS xml)::xml AS c16,
+    XMLCAST('4273535420162021'::bigint::text AS xml)::xml AS c17,
+    XMLCAST(42.007312345678910::text AS xml)::xml AS c18,
+    XMLCAST(42.007312345678910::double precision::text AS xml)::xml AS c19,
+    XMLCAST(true AS xml)::xml AS c20,
+    XMLCAST(false AS xml)::xml AS c21,
+    XMLCAST(42 = 73 AS xml)::xml AS c22,
+    XMLCAST(42 <> 73 AS xml)::xml AS c23,
+    XMLCAST('11:11:11.5'::time without time zone::text AS xml)::xml AS c24,
+    XMLCAST(('11:11:11.5-07'::time with time zone AT TIME ZONE 'Europe/Berlin'::text)::text AS xml)::xml AS c25
+SELECT * FROM view_xmlcast_to_xml;
+ c1 | c2  | c3 | c4 | c5 | c6 |                c7                 |                c8                 |                c9                 |       c10       |       c11       |    c12     |            c13             |            c14             |      c15       |    c16    |       c17        |        c18         |        c19        | c20  |  c21  |  c22  | c23  |    c24     |      c25      
+----+-----+----+----+----+----+-----------------------------------+-----------------------------------+-----------------------------------+-----------------+-----------------+------------+----------------------------+----------------------------+----------------+-----------+------------------+--------------------+-------------------+------+-------+-------+------+------------+---------------
+    | foo |    |    |    |    | foo &amp; &lt;&quot;bar&quot;&gt; | foo &amp; &lt;&quot;bar&quot;&gt; | foo &amp; &lt;&quot;bar&quot;&gt; | foo & <"bar">\r | foo & <"bar">\r | 2024-05-29 | 2024-05-29T12:04:10.703585 | 2024-05-29T12:04:10.703585 | P1Y2M3DT4H5M6S | 427353542 | 4273535420162021 | 42.007312345678910 | 42.00731234567891 | true | false | false | true | 11:11:11.5 | 20:11:11.5+02
+(1 row)
+
+CREATE VIEW view_xmlcast_from_xml AS
+SELECT
+  xmlcast('P1Y2M3DT4H5M6S'::xml AS interval) AS c1,
+  xmlcast('1 year 2 mons 3 days 4 hours 5 minutes 6 seconds'::xml AS interval) AS c2,
+  xmlcast('2002-09-24'::xml AS date) AS c3,
+  xmlcast('2002-09-24+06:00'::xml AS date) AS c4,
+  xmlcast('09:30:10Z'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c5,
+  xmlcast('09:30:10-06:00'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c6,
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c7,
+  xmlcast('2002-05-30T09:30:10Z'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c8,
+  xmlcast('2002-05-30T09:30:10-06:00'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c9,
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c10,
+  xmlcast('foo bar'::xml AS text) AS c11,
+  xmlcast('       foo bar     '::xml AS varchar) AS c12,
+  xmlcast('foo &amp; &lt;&quot;bar&quot;&gt;'::xml AS text) AS c13,
+  xmlcast('42.7312345678910'::xml AS numeric) AS c14,
+  xmlcast('+42.7312345678910'::xml AS numeric) AS c15,
+  xmlcast('-42.7312345678910'::xml AS numeric) AS c16,
+  xmlcast('42'::xml AS integer) AS c17,
+  xmlcast('+42'::xml AS integer) AS c18,
+  xmlcast('-42'::xml AS integer) AS c19,
+  xmlcast('4273535420162021'::xml AS bigint) AS c20,
+  xmlcast('+4273535420162021'::xml AS bigint) AS c21,
+  xmlcast('-4273535420162021'::xml AS bigint) AS c22,
+  xmlcast('true'::xml AS boolean) AS c23,
+  xmlcast('false'::xml AS boolean) AS c24,
+  xmlcast(''::xml AS character varying) AS c25,
+  xmlcast(NULL::xml AS character varying) AS c26;
+\sv view_xmlcast_from_xml
+CREATE OR REPLACE VIEW public.view_xmlcast_from_xml AS
+ SELECT XMLCAST('P1Y2M3DT4H5M6S'::xml AS interval)::interval AS c1,
+    XMLCAST('1 year 2 mons 3 days 4 hours 5 minutes 6 seconds'::xml AS interval)::interval AS c2,
+    XMLCAST('2002-09-24'::xml AS date)::date AS c3,
+    XMLCAST('2002-09-24+06:00'::xml AS date)::date AS c4,
+    (XMLCAST('09:30:10Z'::xml AS time with time zone)::time with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c5,
+    (XMLCAST('09:30:10-06:00'::xml AS time with time zone)::time with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c6,
+    (XMLCAST('09:30:10+06:00'::xml AS time with time zone)::time with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c7,
+    (XMLCAST('2002-05-30T09:30:10Z'::xml AS timestamp with time zone)::timestamp with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c8,
+    (XMLCAST('2002-05-30T09:30:10-06:00'::xml AS timestamp with time zone)::timestamp with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c9,
+    (XMLCAST('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone)::timestamp with time zone AT TIME ZONE 'Europe/Berlin'::text) AS c10,
+    XMLCAST('foo bar'::xml AS text)::text AS c11,
+    XMLCAST('       foo bar     '::xml AS character varying)::character varying AS c12,
+    XMLCAST('foo &amp; &lt;&quot;bar&quot;&gt;'::xml AS text)::text AS c13,
+    XMLCAST('42.7312345678910'::xml AS numeric)::numeric AS c14,
+    XMLCAST('+42.7312345678910'::xml AS numeric)::numeric AS c15,
+    XMLCAST('-42.7312345678910'::xml AS numeric)::numeric AS c16,
+    XMLCAST('42'::xml AS integer)::integer AS c17,
+    XMLCAST('+42'::xml AS integer)::integer AS c18,
+    XMLCAST('-42'::xml AS integer)::integer AS c19,
+    XMLCAST('4273535420162021'::xml AS bigint)::bigint AS c20,
+    XMLCAST('+4273535420162021'::xml AS bigint)::bigint AS c21,
+    XMLCAST('-4273535420162021'::xml AS bigint)::bigint AS c22,
+    XMLCAST('true'::xml AS boolean)::boolean AS c23,
+    XMLCAST('false'::xml AS boolean)::boolean AS c24,
+    XMLCAST(''::xml AS character varying)::character varying AS c25,
+    XMLCAST(NULL::xml AS character varying)::character varying AS c26
+SELECT * FROM view_xmlcast_from_xml;
+                      c1                      |                      c2                      |     c3     |     c4     |     c5      |     c6      |     c7      |            c8            |            c9            |           c10            |   c11   |         c12         |      c13      |       c14        |       c15        |        c16        | c17 | c18 | c19 |       c20        |       c21        |        c22        | c23 | c24 | c25 | c26 
+----------------------------------------------+----------------------------------------------+------------+------------+-------------+-------------+-------------+--------------------------+--------------------------+--------------------------+---------+---------------------+---------------+------------------+------------------+-------------------+-----+-----+-----+------------------+------------------+-------------------+-----+-----+-----+-----
+ @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs | @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs | 09-24-2002 | 09-24-2002 | 11:30:10+02 | 17:30:10+02 | 05:30:10+02 | Thu May 30 11:30:10 2002 | Thu May 30 17:30:10 2002 | Thu May 30 05:30:10 2002 | foo bar |        foo bar      | foo & <"bar"> | 42.7312345678910 | 42.7312345678910 | -42.7312345678910 |  42 |  42 | -42 | 4273535420162021 | 4273535420162021 | -4273535420162021 | t   | f   |     | 
+(1 row)
+
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index 0ea4f50883..1fae5b4b00 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -679,3 +679,281 @@ SELECT xmltext('  ');
 SELECT xmltext('foo `$_-+?=*^%!|/\()[]{}');
 SELECT xmltext('foo & <"bar">');
 SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
+
+-- for xmlcast() tests
+INSERT INTO xmltest
+ VALUES (42,
+'<?xml version="1.0" encoding="utf-8"?>
+ <xmlcast>
+  <period1>P1Y2M3DT4H5M6S</period1>
+  <period2>1 year 2 mons 3 days 4 hours 5 minutes 6 seconds</period2>
+  <date1>2002-09-24</date1>
+  <date2>2002-09-24+06:00</date2>
+  <time>09:30:10.5</time>
+  <time_tz1>09:30:10Z</time_tz1>
+  <time_tz2>09:30:10-06:00</time_tz2>
+  <time_tz3>09:30:10+06:00</time_tz3>
+  <timestamp1>2002-05-30T09:00:00</timestamp1>
+  <timestamp2>2002-05-30T09:30:10.5</timestamp2>
+  <timestamp_tz1>2002-05-30T09:30:10Z</timestamp_tz1>
+  <timestamp_tz2>2002-05-30T09:30:10-06:00</timestamp_tz2>
+  <timestamp_tz3>2002-05-30T09:30:10+06:00</timestamp_tz3>
+  <text1>foo bar</text1>
+  <text2>       foo bar     </text2>
+  <text3>foo &amp; &lt;&quot;bar&quot;&gt;</text3>
+  <decimal1>42.7312345678910</decimal1>
+  <decimal2>+42.7312345678910</decimal2>
+  <decimal3>-42.7312345678910</decimal3>
+  <integer1>42</integer1>
+  <integer2>+42</integer2>
+  <integer3>-42</integer3>
+  <long1>4273535420162021</long1>
+  <long2>+4273535420162021</long2>
+  <long3>-4273535420162021</long3>
+  <bool1 att="true">42</bool1>
+  <bool2 att="false">73</bool2>
+  <empty></empty>
+ </xmlcast>'::xml
+);
+
+-- This prevents the xmlcast regression tests from failing if the system's timezone has been changed.
+SET timezone TO 'America/Los_Angeles';
+
+-- xmlcast exceptions
+\set VERBOSITY terse
+SELECT xmlcast((xpath('//text1/text()', data))[1] AS text[]) FROM xmltest WHERE id = 42;
+SELECT xmlcast((xpath('//text1/integer1()', data))[1] AS int[]) FROM xmltest WHERE id = 42;
+SELECT xmlcast(NULL AS text);
+SELECT xmlcast('foo'::text AS varchar);
+SELECT xmlcast(42 AS text);
+SELECT xmlcast(array['foo','bar'] AS xml);
+\set VERBOSITY default
+
+-- xmlcast tests for "XML to non-XML" expressions
+SELECT
+  xmlcast((xpath('//date1/text()', data))[1] AS date), pg_typeof(xmlcast((xpath('//date1/text()', data))[1] AS date)),
+  xmlcast((xpath('//date2/text()', data))[1] AS date), pg_typeof(xmlcast((xpath('//date2/text()', data))[1] AS date))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//period1/text()', data))[1] AS interval), pg_typeof(xmlcast((xpath('//period1/text()', data))[1] AS interval)),
+  xmlcast((xpath('//period2/text()', data))[1] AS interval), pg_typeof(xmlcast((xpath('//period2/text()', data))[1] AS interval))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//time/text()', data))[1] AS time), pg_typeof(xmlcast((xpath('//time/text()', data))[1] AS time)),
+  xmlcast((xpath('//time_tz1/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz1/text()', data))[1] AS time with time zone)),
+  xmlcast((xpath('//time_tz2/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz2/text()', data))[1] AS time with time zone)),
+  xmlcast((xpath('//time_tz3/text()', data))[1] AS time with time zone), pg_typeof(xmlcast((xpath('//time_tz3/text()', data))[1] AS time with time zone))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS text)),
+  xmlcast((xpath('//text2/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS text)),
+  xmlcast((xpath('//text3/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS text))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS varchar)),
+  xmlcast((xpath('//text2/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS varchar)),
+  xmlcast((xpath('//text3/text()', data))[1] AS varchar), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS varchar))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS name)),
+  xmlcast((xpath('//text2/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS name)),
+  xmlcast((xpath('//text3/text()', data))[1] AS name), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS name))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS bpchar)),
+  xmlcast((xpath('//text2/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS bpchar)),
+  xmlcast((xpath('//text3/text()', data))[1] AS bpchar), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS bpchar))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//decimal1/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal1/text()', data))[1] AS numeric)),
+  xmlcast((xpath('//decimal2/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal2/text()', data))[1] AS numeric)),
+  xmlcast((xpath('//decimal3/text()', data))[1] AS numeric), pg_typeof(xmlcast((xpath('//decimal3/text()', data))[1] AS numeric))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//decimal1/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal1/text()', data))[1] AS double precision)),
+  xmlcast((xpath('//decimal2/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal2/text()', data))[1] AS double precision)),
+  xmlcast((xpath('//decimal3/text()', data))[1] AS double precision), pg_typeof(xmlcast((xpath('//decimal3/text()', data))[1] AS double precision))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//integer1/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer1/text()', data))[1] AS int)),
+  xmlcast((xpath('//integer2/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer2/text()', data))[1] AS int)),
+  xmlcast((xpath('//integer3/text()', data))[1] AS int), pg_typeof(xmlcast((xpath('//integer3/text()', data))[1] AS int))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//long1/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long1/text()', data))[1] AS bigint)),
+  xmlcast((xpath('//long2/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long2/text()', data))[1] AS bigint)),
+  xmlcast((xpath('//long3/text()', data))[1] AS bigint), pg_typeof(xmlcast((xpath('//long3/text()', data))[1] AS bigint))
+FROM xmltest WHERE id = 42;
+
+SELECT
+  xmlcast((xpath('//bool1/@att', data))[1] AS boolean), pg_typeof(xmlcast((xpath('//bool1/@att', data))[1] AS boolean)),
+  xmlcast((xpath('//bool2/@att', data))[1] AS boolean), pg_typeof(xmlcast((xpath('//bool1/@att', data))[1] AS boolean))
+FROM xmltest WHERE id = 42;
+
+SELECT xmlcast((xpath('//empty/text()', data))[1] AS text), pg_typeof(xmlcast((xpath('//empty/text()', data))[1] AS text))
+FROM xmltest WHERE id = 42;
+
+-- xmlcast tests for "XML to XML" expressions
+SELECT
+  xmlcast((xpath('//text1/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text1/text()', data))[1] AS xml)),
+  xmlcast((xpath('//text2/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text2/text()', data))[1] AS xml)),
+  xmlcast((xpath('//text3/text()', data))[1] AS xml), pg_typeof(xmlcast((xpath('//text3/text()', data))[1] AS xml))
+FROM xmltest WHERE id = 42;
+
+-- xmlcast tests for "non-XML to XML" expressions
+SELECT j, pg_typeof(j) FROM xmlcast(NULL AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('foo' AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(''::text AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(NULL::text AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(''::xml AS text) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(NULL::xml AS text) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::text AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::varchar AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('foo & <"bar">'::name AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(xmltext(E'foo & <"bar">\r') AS text) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(xmlcast(E'foo & <"bar">\r' AS xml) AS text) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(to_date('29/05/2024','dd/mm/yyyy') AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('2024-05-29 12:04:10.703585+02'::timestamp with time zone at time zone 'Europe/Berlin' AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('2024-05-29 12:04:10.703585+02'::timestamp without time zone AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(427353542 AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(4273535420162021 AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(42.007312345678910 AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(42.007312345678910::double precision AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(true AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(false AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(42 = 73 AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast(42 <> 73 AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('11:11:11.5'::time AS xml) t(j);
+SELECT j, pg_typeof(j) FROM xmlcast('11:11:11.5'::time with time zone at time zone 'Europe/Berlin' AS xml) t(j);
+
+-- Convert an XML string to bytea and back to xml
+SELECT xmlcast(convert_from(xmlcast('&lt;&quot;foo&amp;bar&quot;&gt;'::xml AS bytea),'UTF8')::xml AS text);
+
+SET xmlbinary TO hex;
+SELECT xmlcast(E'\\xDEADBEEF'::bytea AS xml);
+SET xmlbinary TO base64;
+SELECT xmlcast(E'\\xDEADBEEF'::bytea AS xml);
+
+-- The BY REF and BY VALUE clauses are accepted but ignored.
+-- This checks if the results are indeed the same as without the clauses.
+SELECT
+  xmlcast('foo' AS xml)::text = xmlcast('foo' AS xml BY REF)::text,
+  xmlcast('foo' AS xml)::text = xmlcast('foo' AS xml BY VALUE)::text,
+  xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text BY REF),
+  xmlcast('foo'::xml AS text) = xmlcast('foo'::xml AS text BY VALUE);
+
+SELECT
+  xmlcast(42 AS xml)::text = xmlcast(42 AS xml BY REF)::text,
+  xmlcast(42 AS xml)::text = xmlcast(42 AS xml BY VALUE)::text,
+  xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY REF),
+  xmlcast('42'::xml AS int) = xmlcast('42'::xml AS int BY VALUE);
+
+SELECT
+  xmlcast(42.73 AS xml)::text = xmlcast(42.73 AS xml BY REF)::text,
+  xmlcast(42.73 AS xml)::text = xmlcast(42.73 AS xml BY VALUE)::text,
+  xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS numeric BY REF),
+  xmlcast('42.73'::xml AS numeric) = xmlcast('42.73'::xml AS numeric BY VALUE);
+
+SELECT
+  xmlcast('2024-08-14'::date AS xml)::text = xmlcast('2024-08-14'::date AS xml BY REF)::text,
+  xmlcast('2024-08-14'::date AS xml)::text = xmlcast('2024-08-14'::date AS xml BY VALUE)::text,
+  xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14'::xml AS date BY REF),
+  xmlcast('2024-08-14'::xml AS date) = xmlcast('2024-08-14'::xml AS date BY VALUE);
+
+SELECT
+  xmlcast('12:30:45'::time without time zone AS xml)::text = xmlcast('12:30:45'::time without time zone AS xml BY REF)::text,
+  xmlcast('12:30:45'::time without time zone AS xml)::text = xmlcast('12:30:45'::time without time zone AS xml BY VALUE)::text,
+  xmlcast('12:30:45'::xml AS time without time zone) = xmlcast('12:30:45'::xml AS time without time zone BY REF),
+  xmlcast('12:30:45'::xml AS time without time zone) = xmlcast('12:30:45'::xml AS time without time zone BY VALUE);
+
+SELECT
+  xmlcast('09:30:10+06:00'::time with time zone AS xml)::text = xmlcast('09:30:10+06:00'::time with time zone AS xml BY REF)::text,
+  xmlcast('09:30:10+06:00'::time with time zone AS xml)::text = xmlcast('09:30:10+06:00'::time with time zone AS xml BY VALUE)::text,
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) = xmlcast('09:30:10+06:00'::xml AS time with time zone BY REF),
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) = xmlcast('09:30:10+06:00'::xml AS time with time zone BY VALUE);
+
+SELECT
+  xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml)::text = xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml BY REF)::text,
+  xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml)::text = xmlcast('2002-05-30 09:30:10+06:00'::timestamp with time zone AS xml BY VALUE)::text,
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) = xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone BY REF),
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) = xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone BY VALUE);
+
+SELECT
+  xmlcast('1 year 2 mons'::interval AS xml)::text = xmlcast('1 year 2 mons'::interval AS xml BY REF)::text,
+  xmlcast('1 year 2 mons'::interval AS xml)::text = xmlcast('1 year 2 mons'::interval AS xml BY VALUE)::text,
+  xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 year 2 mons'::xml AS interval BY REF),
+  xmlcast('1 year 2 mons'::xml AS interval) = xmlcast('1 year 2 mons'::xml AS interval BY VALUE);
+
+CREATE VIEW view_xmlcast_to_xml AS
+SELECT
+  xmlcast(NULL AS xml) AS c1,
+  xmlcast('foo' AS xml) AS c2,
+  xmlcast(''::text AS xml) AS c3,
+  xmlcast(NULL::text AS xml) AS c4,
+  xmlcast(''::xml AS text) AS c5,
+  xmlcast(NULL::xml AS text) c6,
+  xmlcast('foo & <"bar">'::text AS xml) AS c7,
+  xmlcast('foo & <"bar">'::varchar AS xml) AS c8,
+  xmlcast('foo & <"bar">'::name AS xml) AS c9,
+  xmlcast(xmltext(E'foo & <"bar">\r') AS text) AS c10,
+  xmlcast(xmlcast(E'foo & <"bar">\r' AS xml) AS text) AS c11,
+  xmlcast(to_date('29/05/2024','dd/mm/yyyy') AS xml) AS c12,
+  xmlcast('2024-05-29 12:04:10.703585+02'::timestamp with time zone at time zone 'Europe/Berlin' AS xml) AS c13,
+  xmlcast('2024-05-29 12:04:10.703585+02'::timestamp without time zone AS xml) AS c14,
+  xmlcast('1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::interval AS xml) AS c15,
+  xmlcast(427353542 AS xml) AS c16,
+  xmlcast(4273535420162021 AS xml) AS c17,
+  xmlcast(42.007312345678910 AS xml) AS c18,
+  xmlcast(42.007312345678910::double precision AS xml) AS c19,
+  xmlcast(true AS xml) AS c20,
+  xmlcast(false AS xml) AS c21,
+  xmlcast(42 = 73 AS xml) AS c22,
+  xmlcast(42 <> 73 AS xml) AS c23,
+  xmlcast('11:11:11.5'::time AS xml) AS c24,
+  xmlcast('11:11:11.5'::time with time zone at time zone 'Europe/Berlin' AS xml) AS c25;
+
+\sv view_xmlcast_to_xml
+SELECT * FROM view_xmlcast_to_xml;
+
+CREATE VIEW view_xmlcast_from_xml AS
+SELECT
+  xmlcast('P1Y2M3DT4H5M6S'::xml AS interval) AS c1,
+  xmlcast('1 year 2 mons 3 days 4 hours 5 minutes 6 seconds'::xml AS interval) AS c2,
+  xmlcast('2002-09-24'::xml AS date) AS c3,
+  xmlcast('2002-09-24+06:00'::xml AS date) AS c4,
+  xmlcast('09:30:10Z'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c5,
+  xmlcast('09:30:10-06:00'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c6,
+  xmlcast('09:30:10+06:00'::xml AS time with time zone) at time zone 'Europe/Berlin' AS c7,
+  xmlcast('2002-05-30T09:30:10Z'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c8,
+  xmlcast('2002-05-30T09:30:10-06:00'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c9,
+  xmlcast('2002-05-30T09:30:10+06:00'::xml AS timestamp with time zone) at time zone 'Europe/Berlin' AS c10,
+  xmlcast('foo bar'::xml AS text) AS c11,
+  xmlcast('       foo bar     '::xml AS varchar) AS c12,
+  xmlcast('foo &amp; &lt;&quot;bar&quot;&gt;'::xml AS text) AS c13,
+  xmlcast('42.7312345678910'::xml AS numeric) AS c14,
+  xmlcast('+42.7312345678910'::xml AS numeric) AS c15,
+  xmlcast('-42.7312345678910'::xml AS numeric) AS c16,
+  xmlcast('42'::xml AS integer) AS c17,
+  xmlcast('+42'::xml AS integer) AS c18,
+  xmlcast('-42'::xml AS integer) AS c19,
+  xmlcast('4273535420162021'::xml AS bigint) AS c20,
+  xmlcast('+4273535420162021'::xml AS bigint) AS c21,
+  xmlcast('-4273535420162021'::xml AS bigint) AS c22,
+  xmlcast('true'::xml AS boolean) AS c23,
+  xmlcast('false'::xml AS boolean) AS c24,
+  xmlcast(''::xml AS character varying) AS c25,
+  xmlcast(NULL::xml AS character varying) AS c26;
+
+\sv view_xmlcast_from_xml
+SELECT * FROM view_xmlcast_from_xml;
\ No newline at end of file
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9ea573fae2..ab1dc2a9bd 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3355,6 +3355,7 @@ XidStatus
 XmlExpr
 XmlExprOp
 XmlOptionType
+XmlCast
 XmlSerialize
 XmlTableBuilderData
 YYLTYPE
-- 
2.34.1