How To - Insert XML Records into a Database

 

Requirement: you want to use BPM Database Activities to insert the value of an XML Data Item into a SQL Server table.

 

Tools: you must be familiar with T-SQL to insert new data into your database table.  You should also familiarize yourself with using Data Items

 

Solution: in order to insert a row of XML data, you must use the Execute SQL Activity, rather than the Create Database Records Activity.  Additionally, you must explicitly set your Data Item containing XML to insert as Unicode by using N apostrophe.  For example, let's assume we have a simple SQL table, Messages, consisting of two columns: ID, a unique identifier (and primary key) and Xml, an Xml data type.  We have a Process with two corresponding Solution Data Items: ID and XmlMessage

 

Within the Execute SQL Activity, the insert query is:

 

 

Notice the use of N' prior to the Data Item: Message (specified as <%Solution.DataItems.Message%>) within the VALUES clause, which denotes explicitly the string that follows is Unicode, allowing it to be inserted into the database as an XML string to an Xml data type.