Convert SQL Result To XML

Added by MarcDEXET, last edited by MarcDEXET on Dec 07, 2006

How to convert SQL Result  to XML ?

import groovy.sql.Sql
import groovy.xml.MarkupBuilder
def schema = "PROD"
def sql = Sql.newInstance("jdbc:oracle:thin:@hostname:1526:${schema}", "scott", "tiger", "oracle.jdbc.driver.OracleDriver")

/* Request */
def req = """
SELECT id,  name, givenname, unit FROM ${schema}.people
WHERE
in_unit=1
AND visible=0
"""
def out = new File('out.xml')
def writer = new FileWriter( out )
def xml = new MarkupBuilder( writer )

xml.agents {
    sql.eachRow( req as String  ) {
        /* For each row output detail */
        row ->
            xml.agent(id:row.id) {
                name( row.name )
                givenname( row.givenname )
                unit( row.unit )
                }
    }
}

Output is 

<agents>                               <!-- xml.agents {                  -->
  <agent id='870872'>                  <!--    agent(id:row.id) {         -->
    <name>ABTI</name>                  <!--       name( row.nom )         -->
    <givenname>Jean</givenname>        <!--       givenname( row.prenom ) -->
    <unit>Sales</unit>                 <!--       unit( row.unite )       -->
  </agent>                             <!--    }                          -->
...
</agents>

Labels

 

Comments (1)

 

  1. Sep 25, 2007

    Marcus Olk says:

    Using GStrings even allows you to use the record set metadata to create the corr...

    Using GStrings even allows you to use the record set metadata to create the corresponding markup:

    xml.agents {
      db.query(req) { rs ->
          while (rs.next()) {
              def md = rs.metaData
              xml.agent {
                  for (i in 1..md.columnCount) {
                      def columnName  = md.getColumnLabel(i)
                      def columnValue = rs.getString(i) != null ? rs.getString(i) : '' 
                      xml."${columnName}"(columnValue)
                  }
              }
          }
       }
  }

    Groovy...