translate xml schema into sql table

db2learner2008 · #1 (**permalink**) 11-29-08, 06:36

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

stolze · #2 (**permalink**) 11-29-08, 07:16

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.

db2learner2008 · #3 (**permalink**) 11-29-08, 09:49

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

stolze · #4 (**permalink**) 11-29-08, 13:18

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.

db2learner2008 · #5 (**permalink**) 11-29-08, 13:33

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

madhu_kaza · #6 (**permalink**) 12-01-08, 02:43

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

stolze · #7 (**permalink**) 12-01-08, 07:06

Before asking here for help, I suggest you pick up a manual and start reading it. That's what they are written for.

Peter.Vanroose · #8 (**permalink**) 12-03-08, 18:12

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

Internet Services

Web Development

Digital Marketing

Consumer Tech