This discussion is archived
8 Replies Latest reply: Nov 26, 2012 10:15 PM by 976056 RSS

XML to PL\SQL Generator

976056 Newbie
Currently Being Moderated
Hi,
Im looking for a tool that would generate the PL\SQL code given a XSD or a WSDL path. there are similar tools for generating java from xml and .NEt from XML. I need something similar to Liquid XML Data Binding product. (http://www.liquid-technologies.com/XmlDataBinding/Xml-Schema-To-VB.aspx).

any help would be gr8.

Thanks.
  • 1. Re: XML to PL\SQL Generator
    odie_63 Guru
    Currently Being Moderated
    Hi,

    AFAIK, there's no such tool for PL/SQL.

    What's the generated PL/SQL code supposed to do?

    XML DB already provides efficient XML loading, parsing techniques, and mappings to Oracle object and collection types (Object-Relational storage).
    That's all described in detail in the documentation : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/toc.htm
  • 2. Re: XML to PL\SQL Generator
    976056 Newbie
    Currently Being Moderated
    A similar tool already exist for every other programing language. It would be very code efficient if at least the skeleton of the procedures is generated. Also it would make it easier to call a web service from PL\SQL .
  • 3. Re: XML to PL\SQL Generator
    odie_63 Guru
    Currently Being Moderated
    Again,

    What's the generated PL/SQL code supposed to do?
  • 4. Re: XML to PL\SQL Generator
    976056 Newbie
    Currently Being Moderated
    All other generated codes in other programming languages don't DO anything. They just generate definitions. Also they create client side objects which makes it easier to communicate with the server objects. In web services generating the skeleton package of the web service is ~50% of the work.
    The scenario that we are facing is this : we have developed lots of web services in Java. these web services do variate of things . some of our clients have oracle solutions. we are not able to integrate our web services into there Oracle solutions because the current Oracle method of consuming the web service is to static, i.e. you have to generate the soap envelop almost manually and then send it.
    so the answer in other scenarios was to generate a client in our customers systems programming language, the WSDL file is already there , its just a mater of generating the code.
    similar to what the program in my firs link does, only in PL\SQL
  • 5. Re: XML to PL\SQL Generator
    odie_63 Guru
    Currently Being Moderated
    Well, PL/SQL is primarily a server-side language to begin with.
    (Oracle Forms uses it too but it has its own PL/SQL engine)

    As said (did you read the links?), XML DB already implements an automatic generation of objects (similar to classes in Java, C#, C++ etc.) based on an XML schema (search for "schema registration" in the guide).

    An incoming XML file can then be converted to an object type, with all its nested complexity.
    The other way around, given an object instance, you can convert it back to an XML document.

    Looks similar to what you're describing?
  • 6. Re: XML to PL\SQL Generator
    976056 Newbie
    Currently Being Moderated
    Can you please provide a valid tutorial link to XML DB, it sounds like the answer.but all the tutorials im finding are not proper.
  • 7. Re: XML to PL\SQL Generator
    odie_63 Guru
    Currently Being Moderated
    XML DB covers a large range of topics : XML schema management, storage strategies, XQuery support, XML indexing, Native Web Services, Repository features etc.

    All these topics are covered in details in the documentation, with sample codes.
    So I strongly suggest you invest some time in reading the chapters of interest.

    This forum also has a FAQ thread.
    The XML DB home page on OTN and the Sample Code page may also provide you with some good real-life examples of XML DB usage :
    http://www.oracle.com/technetwork/database/features/xmldb/index.html
    http://www.oracle.com/technetwork/indexes/samplecode/xmldb-sample-523617.html


    As for the actual discussion, here's a basic example showing what you can do using native XML to SQL mappings :

    1) Registering the schema :

    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb05sto.htm#g1070409
    DECLARE
      v_schema clob := 
    '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
      <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="worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_COLL"/>
        </xs:sequence>
      </xs:complexType>
      <xs:element name="workbook" type="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;
    /
    Oracle automatically generates the object and collection types that map the XML structure to SQL :
    SQL> desc t_workbook
     t_workbook is NOT FINAL
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     worksheet                                          T_WORKSHEET_COLL
    
    SQL> desc t_worksheet_coll
     t_worksheet_coll VARRAY(2147483647) OF T_WORKSHEET
     T_WORKSHEET is NOT FINAL
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
     sheetName                                          VARCHAR2(4000 CHAR)
     sheetId                                            NUMBER(38)
    2) Sample test code :
    SQL> declare
      2  
      3    doc   xmltype := xmltype('<workbook>
      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>
      <worksheet sheetName="Sheet1" sheetId="1"/>
      <worksheet sheetName="new_sheet2" sheetId="2"/>
      <worksheet sheetName="Sheet3" sheetId="3"/>
    </workbook>
    
     
    PL/SQL procedure successfully completed
     
  • 8. Re: XML to PL\SQL Generator
    976056 Newbie
    Currently Being Moderated
    Thank you very much, not quite what i was looking for.
    but your afford is amazing.
    at least now i understand XML DB. :)

Legend

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