How to treat an entry in an XML file as a GString and have groovy still 'evaluate' it?

Question

The 1st example below illustrates the working code. I want to take the working code a step further and store the SQL in an XML file. However, once I read it from an XML file I can't seem to get groovy to treat the SQL statement as a GString anymore.

Here is the working example:

private void testRefCursors(){

 //object owner paramter
 String owner = 'HR';

 //Block of Oracle SQL to execute, returning 4 parameters
 def sqlBlock = 
 """
 declare
  type crsr is ref cursor;
  tables crsr;
  objects crsr;
 begin
  select count(*) into ${Sql.INTEGER} from all_tables where owner= ${owner} ;
  open tables for select * from all_tables where owner= ${owner} ;
  ${Sql.resultSet OracleTypes.CURSOR} := tables;
  select count(*) into ${Sql.INTEGER} from all_objects where owner= ${owner} ;
  open objects for select * from all_objects where owner= ${owner};
  ${Sql.resultSet OracleTypes.CURSOR} := objects;
 end;
 """

//note the order below, is the order of the 'types'  
    //in the SQL block used in the closure as parameters.
sqlSEDREF.call(sqlBlock){
  t,user_tables,o,user_objects ->
  println "found ${t} tables from a total of ${o} objects"
  user_tables.eachRow(){x ->println "table:${x.table_name}"}
  user_objects.eachRow(){println "object:${it.object_name}"}
 }
}

Now When I change the example to read the block of SQL from an XML file; I don't know how (or if it is possible) to treat that value as a GString:

private void testRefCursors(){

 //object owner paramter
 String owner = 'HR';
 def configFile = new File("config.xml");
 config = new XmlSlurper().parse(configFile);
 // FAILURE HERE - 
 GString sqlBlock = config.'sql-test-cursors'


//note the order below, is the order of the 'types'  
    //in the SQL block used in the closure as parameters.
sqlSEDREF.call(sqlBlock){
  t,user_tables,o,user_objects ->
  println "found ${t} tables from a total of ${o} objects"
  user_tables.eachRow(){x ->println "table:${x.table_name}"}
  user_objects.eachRow(){println "object:${it.object_name}"}
 }
}

The error returned is a result of not being able to use the GString implementation of SQL.call(GSTring, Closure):

Caught: groovy.lang.MissingMethodException: No signature of method:groovy.sql.Sql.call() is applicable for argument types: (java.lang.String,tools.UAT$_testRefCursors_closure2)

My current workaround is to perhaps switch from having a config.xml to having a CONFIG.groovy file and read the GStrings from the .groovy file instead of an XML. Any suggestions greatly appreciated.

Answer 1

I think you might try

var sqlBlock = config['sql-test-cursors'].text()

which might return a GString.

Answer 2

The XML file will not return a GString since the GString is constructed by the groovy compiler at compile time. When you parse the XML file you get a String, not a compiled Groovy executable.

If you really want to embed groovy code inside of an XML document (which is what you are really trying to do here) then you should start by looking at this: Embedding Groovy. Basically, you would read in the SQL and treat it as an embedded groovy script.

An alternative (one that I adopted when faced with a similar problem) is to use MessageFormat to substitute values in the XML.

def s = '<sql><query-name>Some SQL</query-name>
           <query>
                select * from {0}
           </query>
         </sql>'

And then after using XmlSlurper on the xml you can substitute values in the XML like this:

assert java.text.MessageFormat (xml.sql.query.text()).format ('dual') 
               == 'select * from dual'