8 Replies Latest reply: Nov 27, 2012 12:15 AM by 976056 RSS

    XML to PL\SQL Generator

      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.

        • 1. Re: XML to PL\SQL Generator

          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
            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

              What's the generated PL/SQL code supposed to do?
              • 4. Re: XML to PL\SQL Generator
                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
                  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
                    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
                      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 :

                      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 :

                        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 name="workbookType" xdb:SQLType="T_WORKBOOK" xdb:maintainDOM="false">
                            <xs:element name="worksheet" type="worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_COLL"/>
                        <xs:element name="workbook" type="workbookType" xdb:defaultTable="WORKBOOK_XML"/>
                          schemaURL => 'workbook.xsd'
                        , schemaDoc => v_schema
                        , local => true
                        , genTypes => true
                        , genTables => true
                        , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                      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
                        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>');
                        9    obj   t_workbook;
                       11  begin
                       13    -- create an object instance from the input XML document :
                       14    doc.toObject(obj, 'workbook.xsd', 'workbook');
                       16    -- update an attribute :
                       17    obj."worksheet"(2)."sheetName" := 'new_sheet2';
                       19    -- save back the object as XML :
                       20    doc := xmltype(obj, 'workbook.xsd', 'workbook');
                       22    dbms_output.put_line(doc.getstringval);
                       24  end;
                       25  /
                        <worksheet sheetName="Sheet1" sheetId="1"/>
                        <worksheet sheetName="new_sheet2" sheetId="2"/>
                        <worksheet sheetName="Sheet3" sheetId="3"/>
                      PL/SQL procedure successfully completed
                      • 8. Re: XML to PL\SQL Generator
                        Thank you very much, not quite what i was looking for.
                        but your afford is amazing.
                        at least now i understand XML DB. :)