Using iBatis to map XML to Objects

[Brian Kierstead <br...@fluidmedia.com>]

I've worked out a way to make iBatis do the dirty work of mapping xml 
into domain objects and thought it be of interest to the group. 

I've had a few instances where I have to turn xml data from some web 
service into objects and I don't like having to do all the work myself.  
This is just for SQL Server, but I'm sure you can create the equivalent 
statements for other servers quite easily.

Brian

First the map:
<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap ...>
    <resultMaps>
        <resultMap id="XmlTestResult" class="XmlTest">
            <result column="CustomerID" property="Id" dbType="varchar(10)"/>
            <result column="ContactName" property="Name" 
dbType="varchar(20)"/>
        </resultMap>
    </resultMaps>

    <statements>
        <select id="XmlTestBase">
            DECLARE @idoc int
            EXEC sp_xml_preparedocument @idoc OUTPUT, '$xml$'
            SELECT * FROM OPENXML (@idoc, '$query$',1) WITH ($columns$)
            EXEC sp_xml_removedocument @idoc
        </select>
   
        <select id="XmlTest" parameterClass="hashtable" 
resultMap="XmlTestResult" extends="XmlTestBase" />
 
  </statements>
</sqlMap>

XmlTestBase does the work and XmlTest sets the result map. The result 
maps must contain the dbType values since they are used to construct the 
$columns$. The $query$ and the $xml$ are passed in from the persistence 
layer and can come from anywhere (url, config file, etc...)

// calling from the persistence layer:
public ArrayList XmlTest()
{
            ArrayList result = new ArrayList();
            
            string xml = "<ROOT>"
                         + "<Customer CustomerID=\"VINET\" 
ContactName=\"Paul Henriot\">"
                         + "<Order OrderID=\"10248\" 
CustomerID=\"VINET\" EmployeeID=\"5\" OrderDate=\"1996-07-04T00:00:00\">"
                         + "<OrderDetail ProductID=\"11\" Quantity=\"12\"/>"
                         + "<OrderDetail ProductID=\"42\" Quantity=\"10\"/>"
                         + "</Order>"
                         + "</Customer>"
                         + "<Customer CustomerID=\"LILAS\" 
ContactName=\"Carlos Gonzlez\">"
                         + "<Order OrderID=\"10283\" 
CustomerID=\"LILAS\" EmployeeID=\"3\" OrderDate=\"1996-08-16T00:00:00\">"
                         + "<OrderDetail ProductID=\"72\" Quantity=\"3\"/>"
                         + "</Order>"
                         + "</Customer>"
                         + "</ROOT>";

            Hashtable hashtable = new Hashtable(3);
            hashtable.Add("xml", xml);
            hashtable.Add("query", "/ROOT/Customer");
            hashtable.Add("columns", GetResultMapColumns("XmlTest"));

            ExecuteFill("XmlTest", hashtable, result);
            
            return result;
}

// Examine the map and retrieve the columns and types used to build the 
$columns$ value in the map
public string GetResultMapColumns(string statementName)
{
            string result = string.Empty;

            // get the map information
            ISqlMapper sqlMap = GetLocalSqlMap();
            IMappedStatement statement = 
sqlMap.GetMappedStatement(statementName);
            RequestScope scope = statement.Statement.Sql.GetRequestScope(
                statement, null, sqlMap.LocalSession);

            // retrive the column list and types from the result map
            for (int i = 0; i < scope.ResultMap.Properties.Count; i++)
            {
                // get the result objects
                ResultProperty resultProperty = scope.ResultMap.Propertiesi;

                // add comma
                if (result != string.Empty)
                    result += ", ";

                // add column name
                result += resultProperty.ColumnName;
                
                // ads the type
                if (resultProperty.DbType != null && 
resultProperty.DbType != string.Empty)
                    result += " " + resultProperty.DbType;
            }

            return result;
}