Using Java - how to cnvert an xml file to SQL?

You need to SAX parse it basically, but it would help if you could post an example of at least part of the xml file

<?xml version="1.0" encoding="UTF-8" ?>
<!-- Created with com.javakit.xml.XmlWriter -->
<!DOCTYPE dasa SYSTEM "dasa.dtd">
<dasa timestamp="2002-04-19 03:28:14 CEST" version="1.0.1">
 <definitions>
    <adindefDefinitions>
      <adindefDefinition adindefCode="Invoicing address" id="04"/>
      <adindefDefinition adindefCode="Main address" id="01"/>
      <adindefDefinition adindefCode="Other Annex 1" id="20"/>
      <adindefDefinition adindefCode="Other Annex 2" id="21"/>
      <adindefDefinition adindefCode="Other Annex 3" id="22"/>
      <adindefDefinition adindefCode="Other Annex 4" id="23"/>
      <adindefDefinition adindefCode="Other Annex 5" id="24"/>
      <adindefDefinition adindefCode="Other Annex 6" id="25"/>
      <adindefDefinition adindefCode="Parts delivery addr" id="03"/>
      </adindefDefinitions>
    <commufuncdefDefinitions>
      <commufuncdefDefinition commufuncdefCode="Direct Fax" id="36"/>
      <commufuncdefDefinition commufuncdefCode="Direct Phone" id="26"/>
      <commufuncdefDefinition commufuncdefCode="Email" id="01"/>
      <commufuncdefDefinition commufuncdefCode="Email Service24h" id="33"/>
      <commufuncdefDefinition commufuncdefCode="Fax" id="02"/>
      <commufuncdefDefinition commufuncdefCode="Homepage" id="32"/>
      <commufuncdefDefinition commufuncdefCode="Mobile" id="27"/>
      <commufuncdefDefinition commufuncdefCode="Phone" id="28"/>
      <commufuncdefDefinition commufuncdefCode="S24h Freecall" id="21"/>
      <commufuncdefDefinition commufuncdefCode="Service24h" id="11"/>
    </commufuncdefDefinitions>
</definitions>
</dasa>


Here is a very very small part of the xml file.
What do I do with each element when it is parsing, how do I use each element when I'm parsing the xml file?

OK, that helps. You would be better of to 'transform' the xml using an xsl stylesheet. Can you tell us how you'd like to use these elements in the SQL?

<?xml version="1.0" encoding="UTF-8" ?>
<!-- Created with com.javakit.xml.XmlWriter -->
<!DOCTYPE dasa SYSTEM "dasa.dtd">
<dasa timestamp="2002-04-19 03:28:14 CEST" version="1.0.1">
 <definitions>
    <adindefDefinitions>
      <adindefDefinition adindefCode="Invoicing address" id="04"/>
      <adindefDefinition adindefCode="Main address" id="01"/>
      <adindefDefinition adindefCode="Other Annex 1" id="20"/>
      <adindefDefinition adindefCode="Other Annex 2" id="21"/>
      <adindefDefinition adindefCode="Other Annex 3" id="22"/>
      <adindefDefinition adindefCode="Other Annex 4" id="23"/>
      <adindefDefinition adindefCode="Other Annex 5" id="24"/>
      <adindefDefinition adindefCode="Other Annex 6" id="25"/>
      <adindefDefinition adindefCode="Parts delivery addr" id="03"/>
      </adindefDefinitions>
    <commufuncdefDefinitions>
      <commufuncdefDefinition commufuncdefCode="Direct Fax" id="36"/>
      <commufuncdefDefinition commufuncdefCode="Direct Phone" id="26"/>
      <commufuncdefDefinition commufuncdefCode="Email" id="01"/>
      <commufuncdefDefinition commufuncdefCode="Email Service24h" id="33"/>
      <commufuncdefDefinition commufuncdefCode="Fax" id="02"/>
      <commufuncdefDefinition commufuncdefCode="Homepage" id="32"/>
      <commufuncdefDefinition commufuncdefCode="Mobile" id="27"/>
      <commufuncdefDefinition commufuncdefCode="Phone" id="28"/>
      <commufuncdefDefinition commufuncdefCode="S24h Freecall" id="21"/>
      <commufuncdefDefinition commufuncdefCode="Service24h" id="11"/>
    </commufuncdefDefinitions>
</definitions>
</dasa>


Here is a very very small part of the xml file.
What do I do with each element when it is parsing, how do I use each element when I'm parsing the xml file?

sorry, for some reason my last post has been submitted twice.

Did you get my question above?

Ideally, I'd like to be able to somehow generate an sql script as done like in this example:

http://www.ibiblio.org/xml/slides/sd2000west/xmlandjava/221.html

http://www.ibiblio.org/xml/slides/sd2000west/xmlandjava/examples/inserts.txt

I found this example (which uses a style sheet like you've suggested), and tried to use  it's style sheet to determine my xml elements and generate an sql statment.

The class I used to call and test this Style sheet was as follows:

package ApplicationCheck;

import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerConfigurationException;

import java.io.*;

public class SimpleTransformer2 {

  public void buildSQLStatement(String xmlFilePath) throws TransformerException, TransformerConfigurationException, FileNotFoundException, IOException{


    System.err.println("xmlFilePath = " + xmlFilePath);

    File f = new File(xmlFilePath);
    FileInputStream fis = new FileInputStream(f);
    BufferedReader br = new BufferedReader(new InputStreamReader(fis));
    String texttemp=null;
    StringBuffer text = new StringBuffer();

    while((texttemp=br.readLine()) !=null)
    {
      text.append(texttemp);
    }
    br.close();
    String xmlString = text.toString();

    System.err.println("xmlString = " + xmlString);
    String outputFile = xmlFilePath.substring(0, (xmlFilePath.length() - 4)) + ".txt";
    System.err.println("outputFile = " + outputFile);

    String xslString = new String("<xsl:stylesheet version=\"1.0\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\"><xsl:output method=\"text\"/>" +
                                  "<xsl:template match=\"Row\">" +
                                  "INSERT INTO test (" +
                                  "<xsl:apply-templates select=\"Field\" mode=\"names\"/>" +
                                  "}" +
                                  "VALUES (" +
                                  "<xsl:apply-templates select=\"Field\" mode=\"values\"/>" +
                                  "}" +
                                  "</xsl:template>" +
                                  "<xsl:template match=\"Field\" mode=\"values\">" +
                                  "'<xsl:value-of select=\".\"/>'<xsl:if test=\"position()!=last()\">, </xsl:if>" +
                                  "</xsl:template>" +
                                  "<xsl:template match=\"Field\" mode=\"names\" >" +
                                  "'<xsl:value-of select=\"@name\"/>'<xsl:if test=\"position()!=last()\">, </xsl:if>" +
                                  "</xsl:template>" +
                                  "</xsl:stylesheet>");

    System.err.println("in a");
    TransformerFactory tFactory = TransformerFactory.newInstance();
    System.err.println("in b");
    Transformer transformer = tFactory.newTransformer(new StreamSource(new ByteArrayInputStream(xslString.getBytes())));
    System.err.println("in c");
    transformer.transform(new StreamSource(new ByteArrayInputStream(xmlString.getBytes())), new StreamResult(new FileOutputStream(outputFile)));
    System.err.println("in d");
  }
}


When I ran this class, I do get data to my outputfile, but it's stil not in any form that I can understand.

I basically just don't know what to do with my parsed data.

Ignoring the example above if you think it's not a good idea to use for my requirements, or using it if you think it's a good help, do you think you can help in what I need to do.

Cheers.

Well i need to know what *your* SQL statements would look like. Just give me some stright SQL strings like:

"INSERT x into y where..."

I know this seems mad to say, but I had just thought there was some magic type of style sheet that could be used to just generate SQL statements from an xml document (I got this impression from the example I mentioned above). I'm not actually sure what the sql ststements would look like.

Basically, all I need to do is to get my data out of the xml document in one of the following formats.

A) An SQL query as mentioned in above.

B) A Column matrix like you'd get in an excel sheet:
   i.e.
       
        Column Nmme1     Column Name 2

        Row 1Value       Row 1Value
        Row 2Value       Row 2Value
        Row 3Value       Row 3Value
        Row 4Value       Row 4Value  
        Row 5Value       Row 5Value

Maybe I'm still not making sense but as I'm, completely new to parsing xml files and don;t fully understand the DTD make up of this file, I just wondered if it was possibel to devise data in some format like on of the above.
I'm probably completely wrong in this, but If you do kinda understand what I'm looking for then help would be greatly appreciated.

       
     

As i understand it, your objective is to take the xml file and then to transform it into an sql statement (correct me if i'm wrong!) so that you can do inserts into a database table. The file appears to contain 'codes' and 'ids'. Which of these would appear in the matrix described above?

Ideally, An sql statemenr would have been great but probably not possible (Note the part of the xml file I've pasted is only a tiny part of it - there is far more data than just codes and id's), but Even without that,
If I could even just get a Vector of result sets for the xml document.

You haven't said *what* data in the xml file must be part of the matrix.

All data

So, does that mean 2 columns, one with a code and the other with an id, such as:

Invoicing address          04
Main address               01
Other Annex 1               20
Other Annex 2               21
Other Annex 3               22
Other Annex 4               23
Other Annex 5               24
Other Annex 6               25
Parts delivery addr          03
Direct Fax               36
Direct Phone               26
Email                    01
Email Service24h          33
Fax                    02
Homepage               32
Mobile                    27
Phone                    28
S24h Freecall               21
Service24h               11

 You first have to start off by identifying which data from the XML file you want into the database. Is it only some specific data or all of it (I saw somewhere that you state all of it)?

  Second you have to create the appropriate tables/columns in the database.

  Third use SAX and only SAX to parse the document.

  Forth open the connection to the database. While you read the data insert it into the appropriate column in the database. When the file is done close the conneciton to the database and you are done.

> If I could even just get a Vector of result sets for the xml document.

  I would not recommend loading all the data in memory. From what I understood you have really big documents and you run the risk of running out of available memory.

  You would significantly help us if you could name us some specific tables/columns in the database and some specific data you want in there so we can start with a small compilable example.

  Hope it helps.