GroovySql and MarkupBuilder: SQL-to-XML

I blogged previously on the ease of use of GroovySql, which is Groovy's easy-to-use JDBC-based data access approach. In another blog post, I covered the utility of OracleXMLQuery, a class which easily generates XML from an Oracle database query. This class makes it very easy, almost trivial, to convert SQL query results into XML, but a major disadvantage of OracleXMLQuery is that it is a proprietary solution. In this blog post, I demonstrate combining GroovySql with Groovy's MarkupBuilder to easily transform database data to XML.

The following script demonstrates the tremendous benefit achieved from the combination of GroovySql (and specifically the groovy.sql.Sql class) and MarkupBuilder. In this script's case, the MarkupBuilder is instantiated with its no-arguments constructor, implying that its markup output is to be written out via System.out.

view plainprint?

1. #!/usr/bin/env groovy  
2. // Be sure to include appropriate JAR with OracleDataSource class on classpath  
3. // (or use the appropriate datasource class if using different driver)  
4.   
5. import groovy.sql.Sql  
6. import java.sql.SQLRecoverableException  
7. Sql sql = null;  
8. try  
9. {  
10.    sql = Sql.newInstance(  
11.       "jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",    
12.       "oracle.jdbc.pool.OracleDataSource")  
13. }  
14. catch (SQLRecoverableException recoverableEx)  
15. {  
16.    println "SQLRecoverableException: ${recoverableEx}"  
17.    println "\tError Code: ${recoverableEx.getErrorCode()}"  
18.    println "\tSQL Status Code: ${recoverableEx.getSQLState()}"  
19.    System.exit(-1)  
20. }  
21.   
22. import groovy.xml.MarkupBuilder  
23. def xml = new MarkupBuilder()  
24. xml.Employees  
25. {  
26.    sql.eachRow("select employee_id, last_name, first_name, email, phone_number from employees") { row ->  
27.       Employee  
28.       {  
29.          Id(row.employee_id)  
30.          LastName(row.last_name)  
31.          FirstName(row.first_name)  
32.          Email(row.email)  
33.          Telephone(row.phone_number)  
34.       }  
35.    }  
36. }  

#!/usr/bin/env groovy
// Be sure to include appropriate JAR with OracleDataSource class on classpath
// (or use the appropriate datasource class if using different driver)

import groovy.sql.Sql
import java.sql.SQLRecoverableException
Sql sql = null;
try
{
   sql = Sql.newInstance(
      "jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",  
      "oracle.jdbc.pool.OracleDataSource")
}
catch (SQLRecoverableException recoverableEx)
{
   println "SQLRecoverableException: ${recoverableEx}"
   println "\tError Code: ${recoverableEx.getErrorCode()}"
   println "\tSQL Status Code: ${recoverableEx.getSQLState()}"
   System.exit(-1)
}

import groovy.xml.MarkupBuilder
def xml = new MarkupBuilder()
xml.Employees
{
   sql.eachRow("select employee_id, last_name, first_name, email, phone_number from employees") { row ->
      Employee
      {
         Id(row.employee_id)
         LastName(row.last_name)
         FirstName(row.first_name)
         Email(row.email)
         Telephone(row.phone_number)
      }
   }
}

When the above script is executed, XML is generated with elements featuring names provided above. A portion of the output is displayed next.

view plainprint?

1. <Employees>  
2.   <Employee>  
3.     <Id>198</Id>  
4.     <LastName>OConnell</LastName>  
5.     <FirstName>Donald</FirstName>  
6.     <Email>DOCONNEL</Email>  
7.     <Telephone>650.507.9833</Telephone>  
8.   </Employee>  
9.   <Employee>  
10.     <Id>199</Id>  
11.     <LastName>Grant</LastName>  
12.     <FirstName>Douglas</FirstName>  
13.     <Email>DGRANT</Email>  
14.     <Telephone>650.507.9844</Telephone>  
15.   </Employee>  
16.   
17.    . . .  
18.   
19.   <Employee>  
20.     <Id>196</Id>  
21.     <LastName>Walsh</LastName>  
22.     <FirstName>Alana</FirstName>  
23.     <Email>AWALSH</Email>  
24.     <Telephone>650.507.9811</Telephone>  
25.   </Employee>  
26.   <Employee>  
27.     <Id>197</Id>  
28.     <LastName>Feeney</LastName>  
29.     <FirstName>Kevin</FirstName>  
30.     <Email>KFEENEY</Email>  
31.     <Telephone>650.507.9822</Telephone>  
32.   </Employee>  
33. </Employees>  

<Employees>
  <Employee>
    <Id>198</Id>
    <LastName>OConnell</LastName>
    <FirstName>Donald</FirstName>
    <Email>DOCONNEL</Email>
    <Telephone>650.507.9833</Telephone>
  </Employee>
  <Employee>
    <Id>199</Id>
    <LastName>Grant</LastName>
    <FirstName>Douglas</FirstName>
    <Email>DGRANT</Email>
    <Telephone>650.507.9844</Telephone>
  </Employee>

   . . .

  <Employee>
    <Id>196</Id>
    <LastName>Walsh</LastName>
    <FirstName>Alana</FirstName>
    <Email>AWALSH</Email>
    <Telephone>650.507.9811</Telephone>
  </Employee>
  <Employee>
    <Id>197</Id>
    <LastName>Feeney</LastName>
    <FirstName>Kevin</FirstName>
    <Email>KFEENEY</Email>
    <Telephone>650.507.9822</Telephone>
  </Employee>
</Employees>

The names of these tags (Employees root tag, Employee row tag, and the individual employee elements) are exactly as specified in the Groovy code.

It is not always preferable to have the generated XML written to standard output. The next Groovy script is a slightly modified version of the above that writes the generated XML to a file whose filename is specified on the command line.

view plainprint?

1. #!/usr/bin/env groovy  
2. // Be sure to include appropriate JAR with OracleDataSource class on classpath  
3. // (or use the appropriate datasource class if using different driver)  
4.   
5. import groovy.sql.Sql  
6. import java.sql.SQLRecoverableException  
7. Sql sql = null;  
8. try  
9. {  
10.    sql = Sql.newInstance(  
11.       "jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",    
12.       "oracle.jdbc.pool.OracleDataSource")  
13. }  
14. catch (SQLRecoverableException recoverableEx)  
15. {  
16.    println "SQLRecoverableException: ${recoverableEx}"  
17.    println "\tError Code: ${recoverableEx.getErrorCode()}"  
18.    println "\tSQL Status Code: ${recoverableEx.getSQLState()}"  
19.    System.exit(-1)  
20. }  
21.   
22. import groovy.xml.MarkupBuilder  
23. MarkupBuilder xml  
24. if (args.length < 1)  
25. {  
26.    xml = new MarkupBuilder()  // no-arguments constructor: System.out  
27. }  
28. else  
29. {  
30.    xml = new MarkupBuilder(new PrintWriter(new File(args[0])))  
31. }  
32.   
33. xml.Employees  
34. {  
35.    sql.eachRow("select employee_id, last_name, first_name, email, phone_number from employees") { row ->  
36.       Employee  
37.       {  
38.          Id(row.employee_id)  
39.          LastName(row.last_name)  
40.          FirstName(row.first_name)  
41.          Email(row.email)  
42.          Telephone(row.phone_number)  
43.       }  
44.    }  
45. }  

#!/usr/bin/env groovy
// Be sure to include appropriate JAR with OracleDataSource class on classpath
// (or use the appropriate datasource class if using different driver)

import groovy.sql.Sql
import java.sql.SQLRecoverableException
Sql sql = null;
try
{
   sql = Sql.newInstance(
      "jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr",  
      "oracle.jdbc.pool.OracleDataSource")
}
catch (SQLRecoverableException recoverableEx)
{
   println "SQLRecoverableException: ${recoverableEx}"
   println "\tError Code: ${recoverableEx.getErrorCode()}"
   println "\tSQL Status Code: ${recoverableEx.getSQLState()}"
   System.exit(-1)
}

import groovy.xml.MarkupBuilder
MarkupBuilder xml
if (args.length < 1)
{
   xml = new MarkupBuilder()  // no-arguments constructor: System.out
}
else
{
   xml = new MarkupBuilder(new PrintWriter(new File(args[0])))
}

xml.Employees
{
   sql.eachRow("select employee_id, last_name, first_name, email, phone_number from employees") { row ->
      Employee
      {
         Id(row.employee_id)
         LastName(row.last_name)
         FirstName(row.first_name)
         Email(row.email)
         Telephone(row.phone_number)
      }
   }
}

The script immediately above writes the generated XML out to a file with the name passed in as a command-argument. If there is no command-line argument provided, the generated XML is written to System.out as was done in the first script.

The MarkupBuilder class allows the developer to specify customization beyond where the generated XML is written. Two such methods are MarkupBuilder.setOmitEmptyAttributes(boolean) and MarkupBuilder.setOmitNullAttributes(boolean). These specify whether null and empty values in the database should be written to the generated XML.


Conclusion

GroovySql and MarkupBuilder are both simple to use while at the same time being highly useful. They are especially powerful together and provide a general approach to presenting database data as XML without proprietary classes specific to any one database vendor.