PL/SQL Object Type to XML with Attribute and Values

I think you mean this:

drop table demo_tab1;CREATE or replace TYPE Amount AS OBJECT ("@ccy" char(3), "Amount" NUMBER);CREATE TABLE demo_tab1 (amt Amount, x xmltype);INSERT INTO demo_tab1 VALUES (Amount('USD',200),null);update demo_tab1 set x = xmltype(amt);

Result:

<AMOUNT ccy="USD">  <Amount>200</Amount></AMOUNT>

Perhaps a bit of further explanation and refinement of what I said would help. Below I simplify the OP's problem to its smallest equivalent case, and I show how we can also simplify the setup (at least for testing; we don't yet know exactly how the OP plans to use all this).

Suppose we want to create a PL/SQL object type that would encode the same data as the following XMLType instance:

<Amount ccy="USD">200</Amount>

We can try this:

create or replace type "Amount" as object ("@ccy" char(3), "Val" number);

But this will generate an outer element called Amount, with an attribute ccy and a nested element called Val:  (notice the simplified way to test the whole thing)

exec dbms_output.put_line(xmltype("Amount"('USD', 200)).getstringval)<Amount ccy="USD"><Val>200</Val></Amount>PL/SQL procedure successfully completed.

We want the value 200 to simply come out as the value of the (leaf) element Amount; but instead, XMLType() creates the sub-element Val within Amount.

By the way, I looked through the documentation to see where the special meaning of @ is explained in this context; I couldn't find it. (I was hoping that, if a similar convention exists for generating scalar values, it would be documented in the same place). Alas, I couldn't find it. Where is that documented?

Of course, one could do what mNem showed, using various combinations of XML-generating functions; but I believe the OP is looking for a way to have to only make changes to the PL/SQL object type, rather than having to also change the code at the same time.

Oracle does allow types (which map to XMLType) such that you have an attribute and an element value, but these are internal types produced via DBMS_XMLSchema.RegisterSchema and an XSD that describes the XML structure.  However, these types seem to be invoked in a special way behind the scenes, like for example when you are using Oracle to store XML Docs.

declare

  doc varchar2(3000) :=

'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"

    xmlns:xdb="http://xmlns.oracle.com/xdb"

    version="1.0"

    xdb:storeVarrayAsTable="false">

  <xs:element name="Charge" type="ChargeType" xdb:defaultTable="CHARGE" />

  <xs:complexType name="AmountType">

    <xs:simpleContent>

      <xs:extension base="xs:short">

        <xs:attribute type="xs:string" name="ccy"/>

      </xs:extension>

    </xs:simpleContent>

  </xs:complexType>

  <xs:complexType name="ChargeType">

    <xs:sequence>

      <xs:element type="AmountType" name="Amount"/>

    </xs:sequence>

  </xs:complexType>

</xs:schema>';

begin

  DBMS_XMLSchema.RegisterSchema(

    SchemaURL => '/Charge/schema/schema1.xsd',

    SchemaDOC => doc);

end;

/

Query the data dictionary, we'll find these implicit types...

CREATE OR REPLACE TYPE PAULZIP."AmountType62_T" AS OBJECT

(

  "SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T",

  "SYS_XDBBODY$" NUMBER (5),

  "ccy" VARCHAR2 (4000 CHAR)

) NOT FINAL INSTANTIABLE

/

CREATE OR REPLACE TYPE PAULZIP."ChargeType61_T" AS OBJECT

(

  "SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T",

  "Amount" "AmountType62_T"

) NOT FINAL INSTANTIABLE

/

Notice the type member names with $ in, like "SYS_XDBBODY$"?  These have special meaning for Oracle, allowing easy mapping so that docs can be loaded into the DB, but I'm not sure invoking an instance of them and casting to XMLType via XMLType constructor will work.  I suspect $ will be illegal.

Anyway, more about the subject is here.

Personally, I think you are approaching it all wrong and I'm not entirely sure I've ever seen problems with numbers of nested levels - so I suspect you are doing something incorrectly.  I'd be approaching it like mNem shows in his example 1, that's the approach Oracle recommends for XML creation.