This discussion is archived
10 Replies Latest reply: Dec 6, 2012 2:49 AM by Anton_ RSS

Generate table/view automatically from xsd

Anton_ Newbie
Currently Being Moderated
Hello all,

following requirement: I receive xml files and appropriate xsd. I don't want to parse the xml by hand (e.g. I know of the possibility to create views from xml with extract - extracting elements manually). Is it any possibility to achieve this:

input: xml file with xsd -> automatical processing: something what I don't know -> output: view. How I already mentioned I just know of the possibility to extract the values manually. After a brief search I found DBMS_XMLSCHEMA.registerSchema but I don't know if it helps me. Registering xsd makes the DB know how the xml look like.

Do you have any ideas?

Thank you in advance for your answers. I appreciate any hint.

Kind regards,
Anton
  • 1. Re: Generate table/view automatically from xsd
    odie_63 Guru
    Currently Being Moderated
    Hi Anton,
    Registering xsd makes the DB know how the xml look like.
    Exactly, but that doesn't make the database know what your requirement is, i.e. how do you want to present query results etc.

    XML schemas can get very complex, with multiple nested levels, repeating groups, substitution groups, recursivity and so on.
    Oracle analyzes the structure of the schema when it registers it, and if you choose the Object-Relational storage option, it will automatically creates SQL object types and collections, as well as object tables to store the data, but still you will need to "manually" interact with the data model.
  • 2. Re: Generate table/view automatically from xsd
    Anton_ Newbie
    Currently Being Moderated
    Hi odie,

    thanks a lot for your answer.

    I think if oracle knows the stucture of a xml file, it can map this file to this structure, right? The same thing if you insert as select * from -> you do not have to explicitely name each column.

    We have some C# developers here. In C#, they put xml and xsd in something called DataSet (a class). And after you can access "tables" build in the object ds -> ds.Tables. This ds.Tables is than kind of PL/SQL associative array or similar.

    Can you think of an approach with PL/SQL?

    Thank you in advance.

    Kind regards,
    Anton
  • 3. Re: Generate table/view automatically from xsd
    odie_63 Guru
    Currently Being Moderated
    And after you can access "tables" build in the object ds -> ds.Tables.
    You still have to know what to query, don't you?

    Does that manage any kind of complexity?
    Can you think of an approach with PL/SQL?
    What you describe is similar to the Object-Relational storage I mentioned above.
    However, accessing the underlying storage objects is not supported by Oracle (doesn't mean you can't though), you have to use the XML abstraction layer to query the data, via XQuery language.


    I'm curious about your requirement. I see more and more people asking how to generate table from XSD, how to automatically read XML with XSD etc.
    What kind of process requires that you work with unknown structure?
  • 4. Re: Generate table/view automatically from xsd
    Anton_ Newbie
    Currently Being Moderated
    You still have to know what to query, don't you?

    Does that manage any kind of complexity?
    No, you don't. But you don't have to program element 1 in column 1, element 2 in column 2 etc. It handles it automatically. You just say insert into <your table 1> ds.Tables["table1 from xml"].

    For us it all about system integration. Different components with similar but not the same data model. We used to handle it via web service offering an interface to the core db. Now the idea is to find another approach.

    I appreciate any idea how not to parse xml manually.

    Thanks in advance and kind regards,
    Anton
  • 5. Re: Generate table/view automatically from xsd
    odie_63 Guru
    Currently Being Moderated
    It handles it automatically. You just say insert into <your table 1> ds.Tables["table1 from xml"].
    So <your table 1> was created automatically too?

    Do you have an example of a typical structure that this approach can handle?
  • 6. Re: Generate table/view automatically from xsd
    Anton_ Newbie
    Currently Being Moderated
    So <your table 1> was created automatically too?
    Or it is an existing one, depends on how different the structures are. I'm not sure I can provide you an example quickly. Need to setup a test case.
  • 7. Re: Generate table/view automatically from xsd
    AlexAnd Guru
    Currently Being Moderated
    >
    But you don't have to program element 1 in column 1, element 2 in column 2 etc. It handles it automatically. You just say insert into <your table 1> ds.Tables["table1 from xml"].
    >
    it's look as
    insert into <table_name>
    select *
       from <some table or view>
    but
    what if table will be altered, say add column
    or
    what if
    select *
       from <some table or view>
    will be return more columns than in <table_name>
    ???
    you get errors


    as for
    >
    ds.Tables["table1 from xml"]
    >
    not sure what is work correctly on complex schema with complex type


    if you have simple xml
    you can try to parse it dynamically:
    - parse xml to find list of tags with path
    - create script which will be generate script for xml like
    select <columns>
    from <table>
    , xmltable (
    ....
    <columns>
    ...
    )
    then create view on the above script

    if your xml structure will be often changing then you must be recreate your script and recreate view

    for xml sample from xsd
    http://www.codeproject.com/Articles/400016/Generate-Sample-XML-from-XSD
  • 8. Re: Generate table/view automatically from xsd
    Anton_ Newbie
    Currently Being Moderated
    Thank you for your input, but what I'm looking for is an alternative to JAXB in PL/SQL. I have already found some threads on JAXB in the DB, but it would be nice to manage it without java if it possibe.

    I would appreciate any hint, how to do it in PL/SQL (JAXB-unmarshalling, or like in C# or whatever).

    Thanks in advance and kind regards,
    Anton
  • 9. Re: Generate table/view automatically from xsd
    odie_63 Guru
    Currently Being Moderated
    Anton,

    As far as I'm concerned, I'm still not sure how generic the solution should be.
    So if possible, a test case describing what you expect would be appreciated.

    It doesn't have to be complex, for example :

    - sample XML
    - corresponding XML schema
    - expected output of the whole process, i.e. what table(s) should be created automatically, which data should be loaded into which table(s) etc.

    Only then we'll be able to say whether it can be done in PL/SQL or not.

    If the purpose is to manage data from different sources, then I suppose at one point, you have to use a common structure?
    Could you clear that up too?
  • 10. Re: Generate table/view automatically from xsd
    Anton_ Newbie
    Currently Being Moderated
    Thank you guys for dealing with my problems. Just to share the experience:

    Since JAXB is integrated within JDeveloper and easy to use we solved this by using a Java web service, which is based on an automatically generated web service from an plsql package (supported by JDeveloper too).

Legend

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