Now this one is a bit out there but maybe you’ll need this someday.

Sometimes clever developers put XML into table fields as BLOB or CLOB (well actually it’s a special BLOB just for XML so standard SQL won’t quite work as expected). Not sure why one would do that but not my place to comment. Anyway it’s a real pain if your holy grail is burried somewhere in that XML.So after some serious googling and trial and error I finally managed to do this in Groovy.

Note that this is for DB2 but should be the same or similar for other DB’s.

import groovy.sql.Sql

String someVariable= vars.get('someVariable')
sql = Sql.newInstance("jdbc:db2://test.com/testdb", "username", "password",
"COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver")

sqlQuery = """
SELECT ID, CAST( XMLSERIALIZE( XMLfield AS CLOB(1m)) AS VARCHAR(32000)) 
AS OutXML FROM testdb
where ID = """ + someVariable

sql.eachRow(sqlQuery) { row ->
    log.info(row.OutXML.toString())
}

sql.connection.close()

As you can see above, should your XML file be bigger than 32k you will run into some issues. There are ways around this. One way is to dig in to the XML code in the SQL statement and only extract elements you want. See XMLQUERY and similar for that.

You can also just do this in a JDBC Request Sampler too. Then you’ll need to trawl through the XML wih the JMeter functions which is a bit limiting. Groovy lets you slurp in the XML or you can just use Regex matching to get what you want.

by Oliver Erlewein