I am using Oracle 19c and trying to convert all data in table t1 into one xml (clob) and then insert all data from that xml (clob) into another table t2 with identical structure.

The problem is that the fields in the table are specified using double quotes and may contain non-valid xml names characters.

Example:

with
    t as
    (
        select 1 as "id", 'abc' as "##name", sysdate     as "date" from dual
        union all
        select 2 as "id", 'qwe' as "##name", sysdate + 1 as "date" from dual
    ),
    x1 as
    (
        select
            xmlelement
            (
                "r",
                xmlattributes
                (
                    "id",
                    "##name",
                    "date"
                )
            )
        from t
    ),
    x2 as
    (
        select
            xmlparse(content '<content><r id="1" _x0023__x0023_name="abc" date="2025-03-18"></r><r id="2" _x0023__x0023_name="qwe" date="2025-03-19"></r></content>') as cl
        from
            dual
    )
select
    *
from
    x2,
    xmltable
    (
        '/content/r'
        passing x2.cl
        columns
            "id"     number         path '@id',
            --"##name" varchar2(4000) path '@##name',
            "date"   date           path '@date'
    ) xt
/

Here you will get ORA-19112/XVM-01003/XPST0003 if you uncomment the line with the parsing of the "##name" field.

Questions:

1. How to correctly and with less effort convert the names of such columns into XML attributes and then convert these attribute names back into column names (this is necessary, since the structure of the target table t2 will be determined by the xml structure)?
   Oracle converts "##name" to "\_x0023__x0023_name", but I can't specify a function in path that would convert "##name"->"\_x0023__x0023_name" to find the attribute, because oracle requires a literal.
2. What is the fastest and most correct way to transform table data into the XML structure you can see in x2? (important: column names must be xml-attribute names)
3. What is the fastest and most correct way to transform the XML data you see in x2 into select/table data?

Oracle documentation on this subject says: .html#GUID-5BE09A7D-80D8-4734-B9AF-4A61F27FA9B2 However, I did not find a way to do the reverse transformation built-in.

I am using Oracle 19c and trying to convert all data in table t1 into one xml (clob) and then insert all data from that xml (clob) into another table t2 with identical structure.

The problem is that the fields in the table are specified using double quotes and may contain non-valid xml names characters.

Example:

with
    t as
    (
        select 1 as "id", 'abc' as "##name", sysdate     as "date" from dual
        union all
        select 2 as "id", 'qwe' as "##name", sysdate + 1 as "date" from dual
    ),
    x1 as
    (
        select
            xmlelement
            (
                "r",
                xmlattributes
                (
                    "id",
                    "##name",
                    "date"
                )
            )
        from t
    ),
    x2 as
    (
        select
            xmlparse(content '<content><r id="1" _x0023__x0023_name="abc" date="2025-03-18"></r><r id="2" _x0023__x0023_name="qwe" date="2025-03-19"></r></content>') as cl
        from
            dual
    )
select
    *
from
    x2,
    xmltable
    (
        '/content/r'
        passing x2.cl
        columns
            "id"     number         path '@id',
            --"##name" varchar2(4000) path '@##name',
            "date"   date           path '@date'
    ) xt
/

Here you will get ORA-19112/XVM-01003/XPST0003 if you uncomment the line with the parsing of the "##name" field.

Questions:

1. How to correctly and with less effort convert the names of such columns into XML attributes and then convert these attribute names back into column names (this is necessary, since the structure of the target table t2 will be determined by the xml structure)?
   Oracle converts "##name" to "\_x0023__x0023_name", but I can't specify a function in path that would convert "##name"->"\_x0023__x0023_name" to find the attribute, because oracle requires a literal.
2. What is the fastest and most correct way to transform table data into the XML structure you can see in x2? (important: column names must be xml-attribute names)
3. What is the fastest and most correct way to transform the XML data you see in x2 into select/table data?

Oracle documentation on this subject says: https://docs.oracle/en/database/oracle/oracle-database/19/adxdb/generation-of-XML-data-from-relational-data.html#GUID-5BE09A7D-80D8-4734-B9AF-4A61F27FA9B2 However, I did not find a way to do the reverse transformation built-in.

• xml
• oracle-database

1 Answer 1

You can change the attribute name in XMLATTRIBUTES:

               xmlattributes
                (
                    "id",
                    "##name" as "name",
                    "date"
                )

and put it back to "##name" in the final XMLTABLE.