XML + JSP + SQL SERVER 2000

[kaye1]

hey i'm new in XML and I'd like to know what I need to do to be able to extract parts of an existing XML document and save it in the database(SQL SERVER). I have minimal knowledge regarding XML although I have read several books about it. I am currently using jsp and sql server2000. What do I need to do first? What do I need (DTDs, parsers, APIs etc.) Can anyone show me a sample code?

 

[sedj]

Why not go to

http://www.java.sun.com

and look at the tutorials for JAXP (Java API for XML Processing). They have sample code.

 

[kaye1]

Sedj, thanks for replying. Anyway, i already checked the site but it's all theories and i can't seem to apply it even the sample codes... anyone has concrete idea/s on what i should do first?

 

[palbano]

>> anyone has concrete idea/s on what i should do first?

Absolutly, do exactly what sedj already said. Here is an excerpt from the JAXP tutorial which is extremely comprehensive and provide thousands of lines of example code such as this:

try {
   DocumentBuilder builder = factory.newDocumentBuilder();
   document = builder.parse( new File(argv[0]) );
} catch (SAXParseException spe) {

>> but it's all theories

I don't know where your from but on planet earth that is not considered "theory".

Now just in case i have not made my point clear, you will not find a better source for learning how to use XML in Java using JAXP, than the tutorial at java.sun.com ;-)

-pete

 

[kaye1]

hey pete, thanks for replying. i've been reading the tutorials from java sun... i was just wondering, what API should I use for my project? SAX or DOM. My project involves reading and storing some elements of an existing XML file inside database.

 

[palbano]

If you want to store a document fragment as a text blob in a column then you would use a DOM parser and then use a XPath query to find the root element of the fragment and then use a XSLT Transformation to transform into a text stream for storage into the database column.

If you want to store row column based values in a traditional database schema you would be best of leveraging any features your database engine might provide. I have not used those features in MS SQL Server but according to documentation they are there. It might go something like my first approach but would end with sending the fragment to the database using the SQL Server feature for inserting XML documents.

If however for some reason you need to build a dynamic SQL Statement or use a Stored Procedure with parameters for each insert then perhaps a SAX parser would give you simpler access to the values for the SQL columns. I would have to know more details to assess that approach completely.

Hope that helps... sometimes my posts are more confusing than helpful


-pete

 

[kaye1]

thanks again. haha you're right... you got me confused there. I think the DOM, XPATH, and XSLT will work with my project. Just to be sure I want to explain my project thoroughly:

I will be developing a system that would generate reports. In order to generate the reports, I would have to extract specific elements from an XML file that is generated by a system independent from the system that i am to develop. The extracted elements will need to be stored in their respective tables in the database.

Will the DOM, XPATH, XSLT work? or is SAX sufficient enough to do this?

Thanks so much for your help.

 

[palbano]

The extracted elements will need to be stored in their respective tables in the database.

Will the DOM, XPATH, XSLT work? or is SAX sufficient enough to do this?

You need to know the XML and Database schemas to answer that question. Potentially both could work, it might just be a matter of which one is simpler to do. Since this is your first endeavor it might not matter. You have a decent hill to climb whichever direction you take.

Another possible technique I believe might be to use XSLT to produce an ASCII delimited file that could be used in a BULK INSERT to SQL Server. If your requirements and data schemas are compatible with that technique it would likely be the simplest.


-pete