aspose file tools*
The moose likes JDBC and the fly likes return as xml document Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "return as xml document" Watch "return as xml document" New topic
Author

return as xml document

Josh Raj
Greenhorn

Joined: Feb 04, 2008
Posts: 7
I want to read datas from tables using stored procedure.Then return the resultant resultset as xml document to the function.Here I can connect to database and read resultset but dont know how to return it as xml document.
Please help me with your answer.
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

You need to build an xml file from the result set . Do you have any knowledge about how to build XML in java ? If no, then better you googled it !


[LEARNING bLOG] | [Freelance Web Designer] | [and "Rohan" is part of my surname]
Josh Raj
Greenhorn

Joined: Feb 04, 2008
Posts: 7
I need to create XML but i need to read values from the database.The fields should be from the table.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41818
    
  62
I think it's understood what you're trying to do. Which part are you having problems with - using JDBC to get the values form the DB, or creating an XML file?
If the latter, the easiest is probably to use the classes in the java.io package to write XML content (particularly File, FileWriter and BufferedWriter).
If the XML structure gets complicated you may wish to use an object model of some kind; several libraries are available to help with that (DOM, XOM, JDOM, dom4j).


Ping & DNS - my free Android networking tools app
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Thats what I said, you read the values from the ResultSet and put those values into CDATA of your XML elements (elements name can be mapped with those DB columns ).
Josh Raj
Greenhorn

Joined: Feb 04, 2008
Posts: 7
Originally posted by Sagar Rohankar:
Thats what I said, you read the values from the ResultSet and put those values into CDATA of your XML elements (elements name can be mapped with those DB columns ).

This is my function:
public String getModels() {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//using callable statement to call a stored procedure in server database.
cs = con.prepareCall("{call getSP(1,1,1)}");
rs = cs.executeQuery();
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = null;
builder = factory.newDocumentBuilder();
Document doc = builder.newDocument();
Element results = (Element) doc.createElement("Results");
doc.appendChild((Node) results);
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
while(rs.next()){
Element row = (Element) doc.createElement("Row");
results.appendChild(row);
for (int ii = 1; ii <= colCount; ii++) {
String columnName = rsmd.getColumnName(ii);
Object value = rs.getObject(ii);
Element node = (Element) doc.createElement(columnName);
node.appendChild((Node) doc.createTextNode(value.toString()));
row.appendChild(node);
System.out.println("NODE"+node);
System.out.println("ROw"+row);

}
i++;
}
return null;
//here i need to return the xml document created
}

In the code I printed value and column name and I get the output the values stored in db. but aftr creating node and printing node its null.I converted node to string value and returnd the string object.The string also is returning null.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

josh, its easier for people if you UseCodeTags.

One question, what database are you using? You are using the JDBC-ODBC bridge which I presume means Microsoft Access? This is a pity, since some databases will return XML from SQL queries without you having to do anything.

Are you remembering that the value of an XML Node is in itself a child of that Node? So your debug lines might be missleading.
[ August 11, 2008: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Josh Raj
Greenhorn

Joined: Feb 04, 2008
Posts: 7
I am using Sql server as db.I am new to java and I am not much familiar to xml.I think my function retrieves value stored in db correctly but the prob is in the xml document creation.Thank you for your help.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

If you are using SQL Server and you don't mind using server specific SQL, append "for xml raw" to the end of your query and the results will come back as an XML string value, pretty much in the format you are trying to build.

Also, the JDBC-ODBC bridge is not a good thing to use if you don't have to. You might try swapping to jTDS or the SQL Serevr JDBC driver Microsoft supply.


I think my function retrieves value stored in db correctly but the prob is in the xml document creation

My point was that:

Will print out the result of the toString() method of Node and Element. Neither of these return the value of the Node or Element. For that you'll need to get the value of the first child object you have of the Node, e.g.:

because in XML the value of a Node is a child node of the Node itself.
[ August 11, 2008: Message edited by: Paul Sturrock ]
Josh Raj
Greenhorn

Joined: Feb 04, 2008
Posts: 7
Thank you all for your replies.
you are right.now i can access my fields as :
[System.out.println("NODE => "+node.getFirstChild().getNodeValue());]
I stored the db values in an xml form and stored to a string and returned.
on returning I thought I can access and read datas as reading from an xml file.
or else i can try as read contents to a string array and return.
Hope this may help me in finding the solutin.
Anywz All the information you provided was reeally valuable.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: return as xml document