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:

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"

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>

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.