By Damjan Kovac
In this article I'm trying to explain one of the method how to build appropriate SQL statement from XML string (document) which represents records in specific DBMS datatable. XSD schemas are not used - reading metadata from DBMS table is used instead.
The basic algorithm is:
1. Generate appropriate XML string representing datarows (eg. from DataTable object)
Each datarow element has 'status' atribute which can have three values:
- INSERT: row has to be inserted in datatable;
- UPDATE: row has to be updated;
- DELETE: row has to be deleted;
Example of XML string representing rows in 'Products' datatable from Northwind SQL Server 2000® database:
<?xml version=”1.0” standalone=”yes”?>
<products>
<datarow status=”INSERT”>
<ProductName>Brocolli</ProductName>
<SupplierID>23</SupplierID>
<CategoryID>19</CategoryID>
<QuantityPerUnit>1kg</QuantityPerUnit>
<UnitPrice>10</UnitPrice>
<UnitsInStock>36</UnitsInStock>
<Discontinued>1</Discontinued>
</datarow>
<datarow status=”DELETE”>
<ProductID>23</ProductID>
</datarow>
<datarow status=”UPDATE”>
<ProductID>45</ProductID>
<CategoryID>11</CategoryID>
<UnitPrice>12</UnitPrice>
</datarow>
</products>
Suitable SQL staments mapped from XML should be:
INSERT INTO Products (SupplierID, CatergoryID, QuantityPerUnit, UnitPrice, UnitsInStock, Discounted) VALUES ('Brocolli', 23, 19, '1kg', 10, 36, 1);
DELETE FROM Products WHERE ProductID=23;
UPDATE Products SET CategoryID=11, UnitPrice=12 WHERE ProductID=45;
2. Get DBMS table metadata to hashtable collection
3. For each row element do:
- parse XML string to hashtable;
- convert column string values to appropriate DBMS column values;
- generate appropriate SQL statement from hashtable and metadata;
Generated SQL statements can be then directly executed on DBMS. This idea can be used in 3-tier application arhitecture where client pass XML data to middle tier component. This component parses XML and do necessary bussiness logic before it generates SQL statements and executes them directy on DBMS.
Here is the simple .NET demo console application written C# (just core algorithm, no additional validation logic).
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Collections;
using System.Text;
/// <summary>
/// Demo for converting XML to SQL statements.
/// It does not contain any additional logic (eg. mandatory columns), just core algorithm
/// Using Northwind demo database ('Products' datatable) on Microsoft SQL Server 2000;
/// Written by Damjan Kovac 2002, damjank@email.si
/// </summary>
class Demo {
/// <summary>
/// Parses XML child elements from root element to Hashtable
/// </summary>
/// <param name="aeRoot"></param>
/// <returns>Values of elements in Hashtable</returns>
public Hashtable ParseToHashtable(XmlElement aeRoot) {
Hashtable lhtCols = new Hashtable();
XmlNodeList lnlElements = aeRoot.GetElementsByTagName("*");
XmlElement leCol = null;
object lsValue = null;
for (int iCol=0; iCol < lnlElements.Count; iCol++) {
leCol = (XmlElement)lnlElements.Item(iCol);
try {
lsValue = leCol.FirstChild.Value;
} catch {
lsValue = null;
}
lhtCols.Add(leCol.LocalName, lsValue);
}
return lhtCols;
}
/// <summary>
/// Return valid SQL field value for specific System.Type
/// </summary>
/// <param name="aoValue">Value to convert</param>
/// <param name="atType">Type of value</param>
/// <returns>Valid SQL string value</returns>
public string GetValidSqlValue(object aoValue, System.Type atType) {
if (aoValue==null)
return null;
string lsValue = (string)aoValue;
if (atType.Equals(System.Type.GetType("System.Char")) ||
atType.Equals(System.Type.GetType("System.String")))
return "'" + lsValue + "'";
else if (atType.Equals(System.Type.GetType("System.Decimal")) ||
atType.Equals(System.Type.GetType("System.Double")) ||
atType.Equals(System.Type.GetType("System.Single")))
return lsValue.Replace(',','.');
else if (atType.Equals(System.Type.GetType("System.Byte")) ||
atType.Equals(System.Type.GetType("System.SByte")) ||
atType.Equals(System.Type.GetType("System.Int32")) ||
atType.Equals(System.Type.GetType("System.Int16")) ||
atType.Equals(System.Type.GetType("System.Int64")) ||
atType.Equals(System.Type.GetType("System.UInt32")) ||
atType.Equals(System.Type.GetType("System.UInt16")) ||
atType.Equals(System.Type.GetType("System.UInt64")))
return lsValue;
else if (atType.Equals(System.Type.GetType("System.DateTime")))
return "'" + lsValue + "'";
else
return lsValue;
}
/// <summary>
/// Main method
/// </summary>
static void Main() {
// XML string can be read from file or generated manualy (eg. from UI DataTable)
// here is just a sample XML string
string lsXml =
"<?xml version=\"1.0\" standalone =\"yes\"?>\n" +
"<products>\n" +
"\t<datarow status=\"INSERT\">\n" +
"\t\t<ProductName>Brocolli</ProductName>\n" +
"\t\t<SupplierID>23</SupplierID>\n" +
"\t\t<CategoryID>19</CategoryID>\n" +
"\t\t<QuantityPerUnit>1kg</QuantityPerUnit>\n" +
"\t\t<UnitPrice>10</UnitPrice>\n" +
"\t\t<UnitsInStock>36</UnitsInStock>\n" +
"\t\t<Discontinued>1</Discontinued>\n" +
"\t</datarow>\n" +
"\t<datarow status=\"DELETE\">\n" +
"\t\t<ProductID>23</ProductID>\n" +
"\t</datarow>\n" +
"\t\t<datarow status=\"UPDATE\">\n" +
"\t\t<ProductID>45</ProductID>\n" +
"\t\t<CategoryID>11</CategoryID>\n" +
"\t\t<UnitPrice>12</UnitPrice>\n" +
"\t</datarow>\n" +
"</products>\n";
string lsTableName = "Products";
string lsRowStatus = null;
string lsIdentityCol = null;
ArrayList lalKeyCols = new ArrayList();
Hashtable lhtCols = null;
Hashtable lhtMetaData = new Hashtable();
string lsSql = null;
// DBMS specific
string lsConString = "Database=Northwind;Server=(local);user id=sa;password=damca";
SqlCommand lCmd = new SqlCommand();
SqlDataReader ldr = null;
Demo myDemo = new Demo();
Console.Write(lsXml);
Console.WriteLine("--------------------------");
try {
lCmd.Connection = new SqlConnection(lsConString);
lCmd.Connection.Open();
// list of key columns
lalKeyCols.Add("ProductID");
// Get DBMS table metadata and identity column if exists
lsSql = " SELECT * FROM " + lsTableName + " WHERE 1=2";
lCmd.CommandText = lsSql;
lCmd.CommandType = CommandType.Text;
ldr = lCmd.ExecuteReader();
DataTable ldt = ldr.GetSchemaTable();
for (int i=0; i < ldt.Rows.Count; i++) {
lhtMetaData.Add(ldt.Rows[i]["ColumnName"], ldt.Rows[i]["DataType"]);
if ((bool)ldt.Rows[i]["IsAutoIncrement"])
lsIdentityCol = ldt.Rows[i]["ColumnName"].ToString().ToLower();
}
ldr.Close();
ldr = null;
// for XML DOM parsing
XmlDocument XmlDoc = new XmlDocument();
XmlDoc.LoadXml(lsXml);
XmlElement leRoot = XmlDoc.DocumentElement;
XmlNodeList lnlDataRows = leRoot.GetElementsByTagName("datarow");
// processing of every datarow
for (int iRows=0; iRows < lnlDataRows.Count; iRows++) {
XmlElement leRow = (XmlElement)lnlDataRows.Item(iRows);
XmlNodeList lnlColumns = leRow.GetElementsByTagName("*");
// status atribute can be only "INSERT", "DELETE", "UPDATE"
lsRowStatus = leRow.GetAttribute("status");
// parse XML elements to Hashtable (lhtCols)
lhtCols = myDemo.ParseToHashtable(leRow);
// if there is no identity column for a key we have to insert it
if (lsIdentityCol == null && lsRowStatus.Equals("INSERT")) {
// find the next value of a key
lsSql = " SELECT MAX(ProductID) + 1 FROM " + lsTableName;
lCmd.CommandText = lsSql;
lCmd.CommandType = CommandType.Text;
ldr = lCmd.ExecuteReader();
if (ldr.Read())
lhtCols.Add("ProductID", ldr.GetInt32(0).ToString());
ldr.Close();
ldr = null;
}
// SQL statement building code section...
StringBuilder lsbUpdate = new StringBuilder();
StringBuilder lsbWhere = new StringBuilder();
bool lbFirstItem = true;
if (lsRowStatus.Equals("UPDATE")) {
lsbUpdate.Append("UPDATE ").Append(lsTableName).Append(" SET ");
lsbWhere.Append(" WHERE ");
object[] lsUpdateCols = new object[lhtCols.Count];
lhtCols.Keys.CopyTo(lsUpdateCols, 0);
for (int col=0; col < lsUpdateCols.Length; col++) {
if (lalKeyCols.Contains(lsUpdateCols[col])) {
lsbWhere.Append(lsUpdateCols[col]).Append(" = ");
lsbWhere.Append(myDemo.GetValidSqlValue(lhtCols[lsUpdateCols[col]], (System.Type)lhtMetaData[lsUpdateCols[col]]));
} else {
if (!lbFirstItem)
lsbUpdate.Append(", ");
lsbUpdate.Append(lsUpdateCols[col]).Append(" = ");
lsbUpdate.Append(myDemo.GetValidSqlValue(lhtCols[lsUpdateCols[col]], (System.Type)lhtMetaData[lsUpdateCols[col]]));
lbFirstItem = false;
} // if
} // for
} else if (lsRowStatus.Equals("DELETE")) {
lsbUpdate.Append("DELETE FROM ").Append(lsTableName);
lsbWhere.Append(" WHERE ");
IEnumerator enumerator = lalKeyCols.GetEnumerator();
while (enumerator.MoveNext()) {
if (!lbFirstItem)
lsbWhere.Append(" AND ");
lsbWhere.Append(enumerator.Current.ToString()).Append(" = ");
lsbWhere.Append(myDemo.GetValidSqlValue(lhtCols[enumerator.Current], (System.Type)lhtMetaData[enumerator.Current]));
lbFirstItem = false;
}
} else {
lsbUpdate.Append("INSERT INTO ").Append(lsTableName).Append(" (");
object[] lsUpdateCols = new object[lhtCols.Count];
lhtCols.Keys.CopyTo(lsUpdateCols, 0);
lsbWhere.Append(" VALUES (");
for (int col=0; col < lsUpdateCols.Length; col++) {
if (lhtCols[lsUpdateCols[col]] == null)
continue;
if (!lbFirstItem) {
lsbUpdate.Append(", ");
lsbWhere.Append(", ");
}
lbFirstItem = false;
if (lhtMetaData[lsUpdateCols[col]] == null)
throw new NullReferenceException("Metadata missing: " + lsUpdateCols[col]);
lsbWhere.Append(myDemo.GetValidSqlValue(lhtCols[lsUpdateCols[col]], (System.Type)lhtMetaData[lsUpdateCols[col]]));
lsbUpdate.Append(lsUpdateCols[col]);
}
lsbUpdate.Append(")");
lsbWhere.Append(")");
}
// print generated SQL statement(s)
Console.WriteLine(lsbUpdate.ToString() + lsbWhere.ToString() + ";");
Console.WriteLine();
} // for iRows
Console.WriteLine("-----------------------");
Console.Write("Press a key...");
Console.ReadLine();
} catch (Exception e) {
Console.WriteLine(e.Message);
Console.ReadLine();
} finally {
if (lCmd.Connection.State == ConnectionState.Open) lCmd.Connection.Close();
}
}
}
