Home > mailing lists

Thread: Using XMLNAMESPACES with XMLEMENT

Using XMLNAMESPACES with XMLEMENT

From

Garfield Lewis

Date:

24 September 2021, 21:43:54

Hi All,

I am attempting to port the following statement from DB2z to Postgres:

SELECT e.empno, e.firstnme, e.lastname,

XMLELEMENT ( NAME "foo:Emp",

XMLNAMESPACES('http://www.foo.com' AS "foo"),

XMLATTRIBUTES(e.empno as "serial"),

e.firstnme,

e.lastname ) AS "Result"

FROM EMP e

WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

Regards,

Garfield

Re: Using XMLNAMESPACES with XMLEMENT

From

Rob Sargent

Date:

24 September 2021, 22:52:14

On Sep 24, 2021, at 3:44 PM, Garfield Lewis <garfield.lewis@lzlabs.com> wrote:

Hi All,

I am attempting to port the following statement from DB2z to Postgres:

SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
            XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
   FROM EMP e
   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

Which Postgres version? Have you tried path function?

Regards,
Garfield

Re: Using XMLNAMESPACES with XMLEMENT

From

Garfield Lewis

Date:

24 September 2021, 22:54:54

I’m using PG v12.6 and no I haven’t tried the path function. Do you have an example?

Regards,

Garfield A. Lewis

From: Rob Sargent <robjsargent@gmail.com>
Date: Friday, September 24, 2021 at 6:52 PM
To: Garfield Lewis <garfield.lewis@lzlabs.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Using XMLNAMESPACES with XMLEMENT

On Sep 24, 2021, at 3:44 PM, Garfield Lewis <garfield.lewis@lzlabs.com> wrote:

Hi All,

I am attempting to port the following statement from DB2z to Postgres:

SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
            XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
   FROM EMP e
   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

Which Postgres version? Have you tried path function?

Regards,
Garfield

Re: Using XMLNAMESPACES with XMLEMENT

From

Rob Sargent

Date:

24 September 2021, 23:37:03

On Sep 24, 2021, at 4:54 PM, Garfield Lewis <garfield.lewis@lzlabs.com> wrote:

I’m using PG v12.6 and no I haven’t tried the path function. Do you have an example?

--
Regards,
Garfield A. Lewis

From: Rob Sargent <robjsargent@gmail.com>
Date: Friday, September 24, 2021 at 6:52 PM
To: Garfield Lewis <garfield.lewis@lzlabs.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Using XMLNAMESPACES with XMLEMENT

On Sep 24, 2021, at 3:44 PM, Garfield Lewis <garfield.lewis@lzlabs.com> wrote:
Hi All,

I am attempting to port the following statement from DB2z to Postgres:

SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
            XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
   FROM EMP e
   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

Which Postgres version? Have you tried path function?
Regards,
Garfield

https://www.postgresql.org/docs/12/functions-xml.html#FUNCTIONS-XML-PROCESSING

Re: Using XMLNAMESPACES with XMLEMENT

From

Pavel Stehule

Date:

25 September 2021, 03:32:48

pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis <garfield.lewis@lzlabs.com> napsal:

Hi All,

I am attempting to port the following statement from DB2z to Postgres:

SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
            XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
   FROM EMP e
   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

I am afraid this is not supported in Postgres. The XMLNAMESPACE clause can be used only in XMLTABLE function. You need to make XML and in the next step you need to modify it as string with string operation.

It can be an interesting feature, and if it is supported by libxml2, then it can be easily implemented. But at this moment it is unsupported, and you have to use string operations - it should not be hard to use regexp.

Regards

Pavel

Regards,
Garfield

Re: Using XMLNAMESPACES with XMLEMENT

From

Garfield Lewis

Date:

26 September 2021, 19:48:37

Thx @Pavel Stehule, I’ll see if I can figure this out… ☺

Regards,

Garfield

From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Friday, September 24, 2021 at 11:33 PM
To: Garfield Lewis <garfield.lewis@lzlabs.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Using XMLNAMESPACES with XMLEMENT

pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis <garfield.lewis@lzlabs.com> napsal:

Hi All,

I am attempting to port the following statement from DB2z to Postgres:

SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
            XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
   FROM EMP e
   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

Regards

Pavel

Regards,
Garfield

Re: Using XMLNAMESPACES with XMLEMENT

From

Pavel Stehule

Date:

27 September 2021, 06:46:44

ne 26. 9. 2021 v 21:48 odesílatel Garfield Lewis <garfield.lewis@lzlabs.com> napsal:

Thx @Pavel Stehule, I’ll see if I can figure this out… ☺

Regards,
Garfield

From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Friday, September 24, 2021 at 11:33 PM
To: Garfield Lewis <garfield.lewis@lzlabs.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Using XMLNAMESPACES with XMLEMENT

Hi

pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis <garfield.lewis@lzlabs.com> napsal:
Hi All,

I am attempting to port the following statement from DB2z to Postgres:

SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
            XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
   FROM EMP e
   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT function. Is there any way to get this to work? I’ve looked at the WITH syntax but it doesn’t look like that will be helpful here.

I am afraid this is not supported in Postgres. The XMLNAMESPACE clause can be used only in XMLTABLE function. You need to make XML and in the next step you need to modify it as string with string operation.

It can be an interesting feature, and if it is supported by libxml2, then it can be easily implemented. But at this moment it is unsupported, and you have to use string operations - it should not be hard to use regexp.

libxml2 supports it - there is an function xmlTextWriterStartElementNS

Postgres supports only the most old version of standard in this area based on ANSI/SQL 2003. This feature was implemented later, maybe in 2006 or 2008. Postgres cannot support more modern standards because used library libxml2 doesn't support XQuery, and there is not any other free (with BSD licence C library). But this feature can be supported, and can be interesting. The implementation probably cannot be trivial, because SQL feature cannot be mapped 1:1 to libxml2 feature, but it is implementable.

I found a workaround - you can use xmlattribute instead - it is same like in Oracle https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Oracle-Compliance-with-SQLXML2011.html#GUID-0D0F19C8-0FB7-4FDD-A55B-18839F340E17 (X080, Namespaces in XML publishing).

postgres=# select xmlelement(name "foo:Emp", XMLATTRIBUTES('http://www.foo.com' as "xmlns:foo"), xmlelement(name "foo:name", 'Pavel'));
┌──────────────────────────────────────────────────────────────────────────────┐
│ xmlelement │
╞══════════════════════════════════════════════════════════════════════════════╡
│ <foo:Emp xmlns:foo="http://www.foo.com"><foo:name>Pavel</foo:name></foo:Emp> │
└──────────────────────────────────────────────────────────────────────────────┘
(1 row)

Regards

Pavel

Regards,
Garfield