Oracle xmlagg with SQLAlchemy-CodePudding

I have a column aggregation scenario where the result could be longer than 4000 characters, so I am trying to switch from listagg to xmlagg.

Here's what I have right now that works as expected:

func.listagg(aggregator, separator).within_group(*order_by)

However, I couldn't find any examples of xmlagg in SQLAlchemy documentation. The following snippet

func.rtrim(func.xmlagg(func.xmlelement(e, column, separator)).extract('//text()').getclobval(), separator)

results in this error, which is understandable:

Uncaught error: Neither 'Function' object nor 'Comparator' object has an attribute 'extract'

Is the xmlagg supported at all in SQLAlchemy? The version I'm using is 1.4.29.

CodePudding user response：

I'm not familiar with SQLAlchemy. However, extract is a (deprecated) Oracle SQL function. So, you might try wrapping the result from func.xmlagg inside a call to func.extract. That result might also need to be inside a call to xmltype.getclobval().

func.rtrim(func.xmltype(func.extract(func.xmlagg(func.xmlelement(e, column, separator)), '//text()')).getclobval())

CodePudding user response：

Is the xmlagg supported at all in SQLAlchemy?

Like any other function, it should work with SQLAlchemy.

>>> from sqlalchemy import func, select
>>>
>>> f1 = func.xmlagg(func.xmlelement("foo_tag", "foo_column_aggregator", 'foo_separator'))
>>>
>>> print(select(f1))
SELECT xmlagg(xmlelement(:xmlelement_1, :xmlelement_2, :xmlelement_3)) AS xmlagg_1

Oracle xmlagg function returs Oracle xmltype ( it is not a varchar neither clob type).

In Oracle SQL:

SQL> SELECT xmlagg(XMLELEMENT("foo_tag",foo,';'))
  2  FROM
  3  (
  4  SELECT 'A1' AS foo FROM dual
  5  UNION ALL SELECT 'A2' AS foo FROM dual
  6  UNION ALL SELECT 'A3' AS foo FROM dual
  7  )
  8  ;

XMLAGG(XMLELEMENT("FOO_TAG",FOO,';'))
--------------------------------------------------------------------------------
<foo_tag>A1;</foo_tag><foo_tag>A2;</foo_tag><foo_tag>A3;</foo_tag>

This part .extract('//text()').getclobval() are methods for Oracle xmltype. It's more like NOSQL syntax (or Oracle DB support for XML syntax) rather then raw SQL syntax, so it's not supported by SQLAlchemy (meaby there are same extension, but i didn't find one). In Oracle it will be:

 SQL> SELECT xmlagg(XMLELEMENT("foo_tag",foo,';')).extract('//text()').getclobval()
  2  FROM
  3  (
  4  SELECT 'A1' AS foo FROM dual
  5  UNION ALL SELECT 'A2' AS foo FROM dual
  6  UNION ALL SELECT 'A3' AS foo FROM dual
  7  )
  8  ;

XMLAGG(XMLELEMENT("FOO_TAG",FOO,';')).EXTRACT('//TEXT()').GETCLOBVAL()
--------------------------------------------------------------------------------
A1;A2;A3;

Same of your options can be:

Live with xml in python (parse xml like this one <foo_tag>A1;</foo_tag><foo_tag>A2;</foo_tag><foo_tag>A3;</foo_tag> in Python )
Write a function in Oracle to convert data from XML format - then call it in python.

Meaby something like this:

SQL> CREATE OR REPLACE FUNCTION extract_from_xmlagg (a_xml IN xmltype) RETURN clob
  2  AS
  3  v_clob clob;
  4  BEGIN
  5  SELECT xmlcast(a_xml AS clob)
  6  INTO v_clob
  7  FROM dual;
  8  RETURN v_clob;
  9  END;
 10  /

Function created.


SQL> SELECT extract_from_xmlagg(XMLAGG(XMLELEMENT("foo_tag",foo,';')))
  2  FROM
  3  (
  4  SELECT 'A1' AS foo FROM dual
  5  UNION ALL SELECT 'A2' AS foo FROM dual
  6  UNION ALL SELECT 'A3' AS foo FROM dual
  7  )
  8  ;
EXTRACT_FROM_XMLAGG(XMLAGG(XMLELEMENT("FOO_TAG",FOO,';')))
--------------------------------------------------------------------------------
A1;A2;A3;

SQL>

Find or write an extension to SQLAlchemy.