This discussion is archived
2 Replies Latest reply: Jan 11, 2013 7:16 AM by Stefano_i0600006 RSS

Object to xml mapping in plsql mantaining namespaces

Stefano_i0600006 Newbie
Currently Being Moderated
Hi,

I have registered an xml schema using DBMS_XMLSCHEMA.registerSchema and setting genType to "true". Pl-sql types have been successfully generated.

I would like to serialize and deserialize in pl-sql code these object types from an xml source or target. I need to mantain namespaces from the registered schema.
In pratice, my requirement is to map xml to pl sql objects (oxm) as provided in JAXB for java.

Thanks
Best regards

Stefano
  • 1. Re: Object to xml mapping in plsql mantaining namespaces
    odie_63 Guru
    Currently Being Moderated
    See : {message:id=10712117}

    Example :
    DECLARE
      v_schema clob := 
    '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"
      targetNamespace="http://xmlns.example.org"
      xmlns:tns="http://xmlns.example.org"
      elementFormDefault="qualified">
      <xs:complexType name="worksheetType" xdb:SQLType="T_WORKSHEET">
        <xs:attribute name="sheetName" type="xs:string"/>
        <xs:attribute name="sheetId" type="xs:integer"/>
      </xs:complexType>
      <xs:complexType name="workbookType" xdb:SQLType="T_WORKBOOK" xdb:maintainDOM="false">
        <xs:sequence>
          <xs:element name="worksheet" type="tns:worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_COLL"/>
        </xs:sequence>
      </xs:complexType>
      <xs:element name="workbook" type="tns:workbookType" xdb:defaultTable="WORKBOOK_XML"/>
    </xs:schema>';
     
    BEGIN
     
      dbms_xmlschema.registerSchema(
        schemaURL => 'workbook.xsd'
      , schemaDoc => v_schema
      , local => true
      , genTypes => true
      , genTables => true
      , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
      );
      
    END;
    / 
    SQL> set serveroutput on
    SQL> 
    SQL> declare
      2  
      3    doc   xmltype := xmltype('<workbook xmlns="http://xmlns.example.org">
      4   <worksheet sheetName="Sheet1" sheetId="1"/>
      5   <worksheet sheetName="Sheet2" sheetId="2"/>
      6   <worksheet sheetName="Sheet3" sheetId="3"/>
      7  </workbook>');
      8  
      9    obj   t_workbook;
     10  
     11  begin
     12  
     13    -- create an object instance from the input XML document :
     14    doc.toObject(obj, 'workbook.xsd', 'workbook');
     15  
     16    -- update an attribute :
     17    obj."worksheet"(2)."sheetName" := 'new_sheet2';
     18  
     19    -- save back the object as XML :
     20    doc := xmltype(obj, 'workbook.xsd', 'workbook');
     21  
     22    dbms_output.put_line(doc.getstringval);
     23  
     24  end;
     25  /
     
    <workbook xmlns="http://xmlns.example.org">
      <worksheet sheetName="Sheet1" sheetId="1"/>
      <worksheet sheetName="new_sheet2" sheetId="2"/>
      <worksheet sheetName="Sheet3" sheetId="3"/>
    </workbook>
    
     
    PL/SQL procedure successfully completed
     
  • 2. Re: Object to xml mapping in plsql mantaining namespaces
    Stefano_i0600006 Newbie
    Currently Being Moderated
    Thank you very much. It 'just what I needed.
    Stefano

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points