Inserting the Data
The easiest (and most fail-safe) method to generate the XML data to be stored in the new column is to use a Document Object Model (DOM) object, create new text nodes in the DOM tree and extract the final XML string from the tree with the xml property (MSMXL, usually used with ASP or ASP.NET) or the saveXML() method (PHP).
A sample code that creates a new record with the addNew method of the ADODB Recordset object and uses a companion DOM object to create the XML string is shown in Listing 3.
The NewXMLObject and NewXMLTextElement functions are parts of the AJAX library that I'm using on most XML-related projects (you can download it from my web site and find a more in-depth description of its functions in the "Introduction to HIJAX" article.
Listing 3 Create a new database record
Set RS = Server.CreateObject("ADODB.Recordset") RS.Open "Products",db,adOpenKeyset,adLockOptimistic RS.AddNew Set XML = NewXMLObject("root",False) RS("ProductName") = "Test" NewXMLTextElement XML,"Color","Red",Null NewXMLTextElement XML,"Size","XL",Null NewXMLTextElement XML,"Fabric","Cotton",Null RS("XMLData").Value = XML.xml RS.Update RS.Close
After the new product has been added to the database, the XMLData field has the value shown in the Listing 4.
Listing 4 Product properties stored as an XML-formatted string