Generating Custom XML Output
Users clicking on one of the quotes trigger a call to the QuoteListSelection Handler, which is outlined in Listing 21-4. This triggers one of three actions, depending on the output format chosen in the combo box. The first action is to send the plain text directly to the output object. The code in Listing 21-8 is called when a quote is selected in the quote list object and the Element XML (Table=Root, Field Name=Element) option is chosen from the output format combo box. The Quote text is passed to the GetSingleQuoteElement class. This
class generates another JDBC query to the SQL Server instance. The query returns a result set containing all of the columns in the quotations table related to the selected quote. Next, the result set and the SQL query string are passed to the buildElementXML class, which is used to build an XML document.
Example:
public String GetSingleQuoteElement(String PassedQuote) {
String XMLDoc=null;
String sql = “SELECT dbo.Quotations.Quotation,
dbo.Authors.AuthorName, dbo.Sources.[Source Name] FROM
dbo.Quotations INNER JOIN dbo.Authors ON dbo.Quotations.AuthorID =
dbo.Authors.AuthorID INNER JOIN dbo.Sources ON
dbo.Quotations.SourceID = dbo.Sources.SourceID WHERE
(dbo.Quotations.Quotation = ‘“+PassedQuote+”’)”;
try {
Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);
Connection conn = DriverManager.getConnection
(“jdbc:microsoft:sqlserver://127.0.0.1:1433;
User=jdbcUser;Password=jdbcUser;
DatabaseName=XMLProgrammingBible”);
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery(sql);
XMLDoc = buildElementXML(rs, sql);
rs.close();
conn.close();
} catch(Exception e) {
e.printStackTrace();
}
return XMLDoc ;
}
the above example shows the buildElementXML class that is used to create a custom element- based XML document for the SQL Server output. We could have used an SQLXML format for our output at this point. Producing SQLXML output would have
needed less code, but would probably result in maintenance issue when a new version of SQLXML is released that changes the format of the output passed to this application. Also, if for some reason we want to change the data source from SQL Server to Oracle, DB2, or another RDBMS, we would have to figure out how to format the XML from the server to meet my needs. By manually writing XML at the application level based on a JDBC result set, you have complete control over the format of the XML that is produced.
The first thing the buildElementXML class does is create a new StringBuffer in which to store the XML document. An XML document declaration is sent to the StringBuffer, along with a root element, called resultset. Next, an element called sql is created, which contains the SQL Server query that was used to generate the result set. We also retrieve the metadata into the XML document, which can be used by applications that work with the XML document to parse the XML values by data type and column name. We also use the metadata column name to name the elements that represent columns in the XML document.
Row data is returned as children of a records element. Because this example returns a single row, a single record element contains the column values. Column values are stored in text data, and column names are represented as element names. The entityRefs class converts any illegal XML characters in the text data (&, ‘, >, <, and “) into legal entity references for those values. The buildElementXML class retrieves the XML document from the String Buffer and returns the XML document to the calling object as a string.
