Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Posts
    23

    Unanswered: translate xml schema into sql table

    Hi experts,
    I have created an xml schema. I want to translate this xml schema into an SQL table in DB2. How can i do this?
    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I have no idea... besides from you being more specific and providing some details??

    Generally, XML is a hierarchical storage structure for data. So you could implement a hierarchy of tables (connected via foreign keys) and then take care of the ordering of XML elements by introducing an additional column in the relational table to hold the element's position.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2008
    Posts
    23

    translate xml schema into sql table

    I can't get what u are trying to explain, but can i get an example of how to creat an SQL table from an XML SCHEMA.

    For example Can yyou give me a code the can translate this XML SCHEMA into SQL Tbale in DB2:
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                targetNamespace="http://xml.netbeans.org/schema/Show"
                xmlns:tns="http://xml.netbeans.org/schema/Empl"
                elementFormDefault="qualified">
        <xsd:simpleType name="Empl_interger">
            <xsd:restriction base="xsd:positiveInteger">
                <xsd:maxInclusive value="2147483647"/>
            </xsd:restriction>
        </xsd:simpleType>
        <xsd:complexType name="Empl_Complex">
            <xsd:sequence>
                <xsd:element name="TID" type="tns:Show_interger"></xsd:element>
                <xsd:element name="TimeID" type="tns:Empl_interger"></xsd:element>
                <xsd:element name="MID" type="tns:Empl_interger"></xsd:element>
                <xsd:element name="TCID" type="tns:Empl_interger"></xsd:element>
            </xsd:sequence>
        </xsd:complexType>
        <xsd:element name="Show" type="tns:Empl_Complex"></xsd:element>
    
    </xsd:schema>
    Can you please help me with the Sql code for this. I need to create it in netbeans.
    Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The way I read this, you have an XML type Empl_Complex (I'll ignore for whatever reason you abbreviate the names). That XML type has for nested elements. Mapping that to a relational model is straight-forward: create a single table that has 4 columns. When processing XML documents that conform to this XSD, you take the values of the 4 elements and store them together in a single new row. I don't think any further explanations or code samples are necessary. You work with SQL and XML, so you should (or already do) know the basic concepts of both technologies.

    Now, one question I would understand is if there are tools to handle the shredding of XML documents into such a relational table. And the answer to that question depends on the version of DB2 you are using on some platform - both of which you didn't tell us. Assuming that you are using DB2 LUW V9.5, you can for example do the shredding using the pureXML functionality built into DB2. Have a look at the manual which also gives you some examples for such basic tasks.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2008
    Posts
    23

    translate xml schema into sql table

    Thanks for your prompt reply. Actually i am just starting to work on SQL and XML and i am new in these two technologies. I am trying to get help from you experts so that i can learn from your help.

    I would be grateful if i can have a sample code that can do what you are trying to explain. It is true is a basic and simple think to do, but as i said i am completely new on these areas so i am finding it difficult to implement. this is why i am seeking help from you. I need to do the creat SQL table in netbeans but done know how to do it. The table i am trying to create is called Empl.Can you please help me on this? I am using DB2 v.9.5.
    Thanks

  6. #6
    Join Date
    Apr 2008
    Posts
    39
    Shredding an XML document is supported from DB2 V9 onwards.

    You can refer to xmldecomposition sample in
    sqllib/samples/xml/clp/xmldecomposition.db2

    This sample explains how we can shred an XML document into relational tables

    Thanks,
    Madhavi

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Before asking here for help, I suggest you pick up a manual and start reading it. That's what they are written for.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2learner2008
    I have created an xml schema. I want to translate this xml schema into an SQL table in DB2. How can i do this?
    If you want a generic transformation from an XML Schema to a "CREATE TABLE" statement (which would work on your XML Schema but also on any other, similar one), I've good news: I'm currently writing an article on this. It will appear on our website (http://www.abis.be/) within a few days. The article will be in Dutch, but the conversion algorithm (actually, it's XSLT) will also work if you're non Dutch speaking
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •