Pushdown SQL/XML ---------------- Key: TEIID-4452 URL: https://issues.jboss.org/browse/TEIID-4452 Project: Teiid Issue Type: Enhancement Components: JDBC Connector Affects Versions: 9.x Reporter: Tom Arnold Fix For: 10.x It would be helpful to pushdown SQL/XML functions for JDBC databases that support them. Databases: - Oracle - Postgres Functions: - XMLELEMENT - XMLATTRIBUTES - XMLAGG - XMLCONCAT We have an application that uses these functions and we support Oracle, Postgres, and Teiid (to integrate external data sources). Here's an example query generated by this application: {code:xml} select "ITEMS"."ITEM_ID" "ITEM_ID", ( select xmlelement ( name "attributes", xmlagg( xmlelement( name "attribute", xmlattributes( "ITEM_ATTRIBUTES"."ATTR_NAME" as "name", "ITEM_ATTRIBUTES"."ATTR_VALUE" as "value" ) ) ) ) from "ITEM_ATTRIBUTES" "ITEM_ATTRIBUTES" where ( "ITEMS"."ITEM_ID" = "ITEM_ATTRIBUTES"."ITEM_ID" ) ) "ITEM_ATTRIBUTES" {code} {code} ITEM_ID ITEM_ATTRIBUTES 3 <attributes><attribute name="foo" value="bar"></attribute><attribute name="baz" value="blah"></attribute></attributes> 4 <attributes><attribute name="foo" value="123"></attribute><attribute name="baz" value="456"></attribute></attributes> {code} When we are using Teiid queries like this do not work well because they cannot be pushed down, so we get N-many queries (one per row) and the query ends up taking much longer than if performed directly against Oracle/Postgres. Since SQL/XML is standard and supported by both Oracle/Postgres it would be good if queries like this could be pushed in their entirety so that there would be no performance difference between going through Teiid and native.