Forum Stats

  • 3,855,382 Users
  • 2,264,500 Discussions
  • 7,905,981 Comments

Discussions

PL/SQL Object Type to XML with Attribute and Values

Apex_Noob
Apex_Noob Member Posts: 59 Bronze Badge
edited Jan 20, 2020 11:35AM in SQL & PL/SQL

Hi,

I have to create XML with too many level of nesting. For that we found creating PL/SQL object type and converting it to xml is convenient and the code is more readable. However we are facing an issue, when we have to generate both value and attribute for a tag.

Example using object I am able to generate below xml:-

1.   <Charge><Amount>200</Amount></Amount>      -----> Amount tag without attribute

create or replace type "Charge" as object("Amount" number);

2.   <Charge><Amount ccy="USD"/> </Charge>         ------> Amount Tag with attribute

create or replace type "Charge" as object("@Amount" number);

However, I am not able to create object to generated below xml

3.  <Charge> <Amount ccy="USD">200</Amount></Charge>

How, to create PL/SQL Object type so that it should be able to generate tag with both attribute and value as in point 3.

Thanks and Regards,

Shijesh

Tagged:

Answers

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jan 16, 2020 10:10PM

    It may help to post what your attempt so far.

  • Apex_Noob
    Apex_Noob Member Posts: 59 Bronze Badge
    edited Jan 18, 2020 4:58AM

    Bump??? Is this feature support using PL/SQL type or shall we move our code to Java Stored Procedure ???

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jan 18, 2020 7:18AM

    There are number of ways to create xml in sql ...

    1.

    set serveroutput on;
    declare
    v_ccy varchar2(3) := 'USD';
    v_amt number := 200;
    v_xml xmltype;begin  select  xmlelement
              (
                "Charge",
                xmlelement
                (
                  "Amount",
                  xmlattributes(v_ccy as "ccy"),
                  v_amt
                )
              )  into v_xml        
      from dual;  dbms_output.put_line(v_xml.getClobVal());
    end;

    2.

    set serveroutput on;DECLARE  ccy varchar2(3) := 'USD';  amt number := 200;  xml xmltype;  BEGIN  select x.column_value into xml from xmltable(  '  <Charge>    <Amount ccy="{$ccy}">{$amt}</Amount>  </Charge>  '  passing ccy as "ccy"        , amt as "amt"  ) x  ;  dbms_output.put_line(xml.getClobVal());END;

    3.

    set serveroutput on;DECLARE  ccy varchar2(3) := 'USD';  amt number := 200;  xml xmltype;  BEGIN  select x.column_value into xml from xmltable(  '    element Charge {      element Amount {        attribute ccy {$ccy}        ,        $amt      }    }    '  passing ccy as "ccy"        , amt as "amt"  ) x  ;    dbms_output.put_line(xml.getClobVal());  END;
  • cormaco
    cormaco Member Posts: 1,990 Silver Crown
    edited Jan 18, 2020 7:39AM

    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>
  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Jan 18, 2020 4:14PM
    cormaco wrote:I think you mean this:
    1. droptabledemo_tab1;
    2. CREATEorreplaceTYPEAmountASOBJECT("@ccy"char(3),"Amount"NUMBER);
    3. CREATETABLEdemo_tab1(amtAmount,xxmltype);
    4. INSERTINTOdemo_tab1VALUES(Amount('USD',200),null);
    5. updatedemo_tab1setx=xmltype(amt);
    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:
    1. <AMOUNTccy="USD">
    2. <Amount>200</Amount>
    3. </AMOUNT>
    <AMOUNT ccy="USD">  <Amount>200</Amount> </AMOUNT> 

    This seems close to the OP's request - if I understand correctly, he would like the XML object model to be encoded in the PL/SQL object type, so that the XMLType() constructor applied to such objects will build the correct XML automatically.

    Alas, your solution puts the attribute in the outer element (the OP called it "Charge", you use AMOUNT, but other than that, that's what your code does, compared to the OP's request). The problem, it seems to me - without knowing much about creation of XMLType instances out of PL/SQL objects - is that you need a way to name PL/SQL object attributes so that, when XMLType() operates on them, they only produce a scalar value, not a complete element. The "Amount" object could have attributes "@ccy" (which will produce an XML attribute) and perhaps "#val" (or some similar convention) to produce just the value 200 for the "Amount" element - not a complete sub-element of "Amount". Again, I don't know XMLType() - I just experimented with # and a few others and I know they don't work; is there some other convention, similar to @ for XML attributes (instead of elements), that will produce a scalar value in the XMLType instance, instead of a full sub-element?

  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Jan 18, 2020 5:09PM

    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.

    Now, by calling the first member of Amount "@ccy" - with a leading "at" sign - we are indicating to XMLType() that we intend that member of the PL/SQL object type as an XML attribute, rather than an element. Is there a similar marker to tell XMLType() that the second member of the PL/SQL object type is meant as the value of the Amount XML element? As I said, I tried a few things - "#Val", "\Val", "?Val" etc. - none of which worked.

    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.

  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Jan 18, 2020 7:53PM

    This can be simplified even further. Never mind the XML attribute business. The question is really about creating a PL/SQL object type that can encode an XMLType instance like this:

    <Amount>200</Amount>

    What is the object type definition - perhaps the type's name will be "Amount" - that will generate this XML element, when called like

    xmltype("Amount"(200))

    ?

  • Paulzip
    Paulzip Member Posts: 8,759 Blue Diamond
    edited Jan 18, 2020 8:38PM

    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.

  • cormaco
    cormaco Member Posts: 1,990 Silver Crown
    edited Jan 19, 2020 3:38AM
    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?

    I couldn't find any documentation either, the example I posted is mostly derived from the OPs original post and some testing. I had never used this feature before and wasn't even aware of it.

    As to create an xml fragment like this: <Amount ccy="USD">200</Amount>, this might not be possible using this feature.

  • Apex_Noob
    Apex_Noob Member Posts: 59 Bronze Badge
    edited Jan 20, 2020 11:35AM

    Hi All,

    Thanks all of you for help.

    We are using Java Stored procedure approach using JAXB.

    Thanks and Regards,

    Shijesh