Looking for some opinions on how to write effective XML

[Chris Stewart]

Say you were going to write an XML file that replaced the framework of a database. How would you guys go about this? Here is where I am now...

<?xml version="1.0"?>
<database>
<databaseName>JavaDB</databaseName>
<tables>
<tableName>users</tableName>
<columns>
<data name="userName" type="VARCHAR(25)" />
<data name="userPassword" type="VARCHAR(25)" />
</columns>
<tableName>books</tableName>
<columns>
<data name="bookName" type="VARCHAR(25)" />
<data name="bookGenre" type="VARCHAR(25)" />
</columns>
</tables>
</database>

In the end I'd like to be able to write an application that will build the database outlined in my xml file. Any advice on xml creation or parser usage? Thanks in advance.
[ June 24, 2002: Message edited by: Mapraputa Is ]

[Karthik Guru]

nice idea.
Are you trying to create .sql files with create table statements after parsing the xml?
u have figured out a neat of learning xml, parsing concepts! great.

<?xml version="1.0"?>
<database>
<name>JavaDB</name>
<tables>
<table>
<name>users</name>
<columns>
<column name="userName" type="VARCHAR(25)" />
<column name="userPassword" type="VARCHAR(25)" />
</columns>
</table>
<table>
<name>books</name>
<columns>
<column name="bookName" type="VARCHAR(25)" />
<column name="bookGenre" type="VARCHAR(25)" />
</columns>
</table>
</tables>
</database>

then you might want to make some columns of type ID? (ie primary key?)
refer to them from other places to specify foreign key constraints? so they w'd be of the type IDREF. An attribute of type IDREF can take any value that is of the type ID
So i don't think it can give the effect of a database constraint though.
If somebody can throw some light on using xml as database, that will be great!
Some thoughts here..
If we have an element named <database>, i tend not to repeat the same name while declaring an element (for eg name). My feeling is that we can probably have <name> instead of <databaseName>. Please feel free ignore these suggestions though as am not sure if i can safely extend java conventions to xml as well !
Actually lot of people whom i know prefer this convention when naming java packages as well.
com.i2.iquote.Controller
to
com.i2.iquote.IQuoteController
class Customer{
getName() instead of getCustomerName()
}
in the above cases Customer and IQuote have been found to be redundant.
Frankly, such tendencies to extend concepts picked up from a different environment
backfired in other places.
I can clearly remember that Python's way of using exceptions seemed to be quite different from that of java and i was told not to carry my java knowledge to python.
i can already see some problems if we are looking for ,say, the name of a column.
Doing
doc.getElementsByTagName("columName"); w'd fetch all the column names
whereas if i follow what i said eariler, i need to get to "column" element first before doing getElementsByTagName("Name");
can someone point out the conventions used while naming xml elements?
thanks!
[ June 24, 2002: Message edited by: Mapraputa Is ]

[Mapraputa Is]

You can just use attributes to carry names, as you did with <data> tag. Then your XML will be

<?xml version="1.0"?>
<database name="JavaDB">
<table name="users">
<column name="userName" type="VARCHAR(25)" />
<column name="userPassword" type="VARCHAR(25)" />
</table> 
<table name="books">
<column name="bookName" type="VARCHAR(25)" />
<column name="bookGenre" type="VARCHAR(25)" />
</table> 
</database>

In the end I'd like to be able to write an application that will build the database outlined in my xml file.
If you want to generate SQL command to create tables, then you can use XSLT and your framework will look like

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="database">
USE <xsl:value-of select="./@name"/>;
<xsl:for-each select="table">
CREATE TABLE <xsl:value-of select="./@name"/> (
id BIGINT NOT NULL AUTO_INCREMENT,
<xsl:for-each select="column">
<xsl:value-of select="./@name"/><xsl:text> </xsl:text>
<xsl:value-of select="./@type"/>,
</xsl:for-each>
PRIMARY KEY (id))
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

output:

USE JavaDB;

CREATE TABLE users (
id BIGINT NOT NULL AUTO_INCREMENT,
userName VARCHAR(25),
userPassword VARCHAR(25),

PRIMARY KEY (id))

CREATE TABLE books (
id BIGINT NOT NULL AUTO_INCREMENT,
bookName VARCHAR(25),
bookGenre VARCHAR(25),

PRIMARY KEY (id))

I cannot guarantee my SQL is correct, though.
But I am sure there are tools for "XML to SQL" mapping, you may want to investigate, unless it's an educational exercise.
[ June 24, 2002: Message edited by: Mapraputa Is ]

[Chris Stewart]

Wow, this is looking really good. I'm just trying to make an easy way to create a database script from a Java app. I've never really seen anything on XSLT, would anyone feel like giving a quick overview and common uses? Thanks guys, you're helping a lot.
Mapraputa Is, in your example above, how did you have the xsl file use the xml file for finding the values?
[ June 24, 2002: Message edited by: Chris Stewart ]

[Mapraputa Is]

I am using Xalan for XSL transformations and I am just calling it from command line:
java org.apache.xalan.xslt.Process -in [xml file] -xsl [xsl file] -out [output file]
But it can be called from your Java program also.

[Chris Stewart]

I got everything working like I wanted. This will create a database from the framework you give in an XML file. Here you go...
XML File

<?xml version="1.0"?>
<database name="JavaDB">
<table name="users">
<column name="userName" type="VARCHAR(25)" />
<column name="userPassword" type="VARCHAR(25)" />
</table> 
<table name="books">
<column name="bookName" type="VARCHAR(25)" />
<column name="bookGenre" type="VARCHAR(25)" />
</table>
</database>

XSL File

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl :o utput method="text"/>
<xsl:template match="database">
CREATE DATABASE <xsl:value-of select="./@name"/>;
USE <xsl:value-of select="./@name"/>;
<xsl:for-each select="table">
CREATE TABLE <xsl:value-of select="./@name"/> (
id BIGINT NOT NULL AUTO_INCREMENT,
<xsl:for-each select="column">
<xsl:value-of select="./@name"/><xsl:text> </xsl:text>
<xsl:value-of select="./@type"/>,
</xsl:for-each>
PRIMARY KEY (id));
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Java Class

import javax.xml.transform.*;
import javax.xml.transform.stream.*;
import java.io.FileOutputStream;
import javax.xml.parsers.*;
/**
 * Title:
 * Description:
 * Copyright:    Copyright (c) 2002
 * Company:
 * @author
 * @version 1.0
 */
public class Xml {
  public void startTrans() {
    try {
      // 1. Instantiate a TransformerFactory.
      TransformerFactory tFactory = TransformerFactory.newInstance();
      // 2. Use the TransformerFactory to process the stylesheet Source and
      //    generate a Transformer.
      Transformer transformer = tFactory.newTransformer (new StreamSource("C:/development/DBParse/parseDB.xsl"));
      // 3. Use the Transformer to transform an XML Source and send the
      //    output to a Result object.
      transformer.transform (new StreamSource("C:/development/DBParse/parseDB.xml"), new StreamResult(new FileOutputStream("C:/development/DBParse/parseDB.out")));
    } catch (TransformerConfigurationException ex) {
      System.out.println("TransformerConfigurationException thrown: " + ex);
    } catch (TransformerException ex) {
      System.out.println("TransformerException thrown: " + ex);
    } catch (Exception ex) {
      System.out.println("Exception thrown: " + ex);
    }
  }
}

[ June 25, 2002: Message edited by: Chris Stewart ]