Using XML Data Types in Order System
ravikanth | May 13, 2009I saw a post in commerce communities asking how to use XML data types in commerce server order system. Commerce server (CS) does have some constraints on data types. Since CS is working with SQL Server, we should not assume that it supports all the data types supported by SQL server. Click below links to know more about the supported .NET and SQL Server data types.
http://msdn.microsoft.com/en-us/library/aa545934.aspx
http://msdn.microsoft.com/en-us/library/dd464501.aspx
If your question is “I have to use XML Data type” and the straight forward answer is “No”. I have small work around for this problem – and it may or may not work for you.
- Create two columns in database
- column1 of type nvarchar(max)
- column2 of type xml
- Extend order system as explained in MSDN site add map the XML to the column 1 (assuming that you are storing serialized object data in column 1).
- Write a database trigger to update Column 2 when Column 1 changes.
At a given point of time, data in both columns will be same. Whenever you want to use XML Search feature then you can use column 2 and whenever you want to work on XML data through C# then you can get serialized data from column1, desterilize back and manipulate the object.
Ravi ,
Have you ever tried using triggers on the order tables?
Won’t i get performance issues ?
thanks
arik
Arik, it depends on the data load you are having on the site. Ideally we archive old information to another server (may be 3 months old). In that context the order tables will not have not much information and a trigger will not cause any performance issue. If you are keeping all old information in the same table, this approach may create some performance issues in long run. That is the reason, I mentioned – “The solution may or May not work for you”.
Hope this helps.