SQL2005 OPENXML and Custom Arrays
Cristian Merighi ()
Use XML data type and relevant T-SQL statements, available since SQL 2005, to handle the insertion of an entire list of element with just one instruction.
This article is obsolete. Some functionalities might not work anymore. Comments are disabled.
In an enterprise application developed using object oriented design pattern, you have to care about custom entities and
hierarchical relations among them. Your database schema is designed to match those relations and store the data kept by each of those entities.
ORM Tools take care to map these bindings between your database and the logic layer you designed, they also may introduce performance issues
because of loops of (SQL) commands meant to save a set of entities' data into the underlying database.
I personally tend to avoid to blindly rely on autogenerated SQL stuff (let's say in a common Linq to SQL/Entities scenario),
I prefer to point my commands to some personally written stored procedures trying to exploit the succulent and powerful features of the database technology.
I'm now designing a new blog engine as a pretext to learn some new aspects of the .Net technologies and also to tune Data Access aspects.
With this article I want to share the method I've used to insert - at once - an entire collection of business entities into the database
with just one INSERT INTO... SELECT statement.
The key is the OPENXML T-SQL instruction, and a preventive xml serialization process.
The idea is to serialize into an xml object a list of custom entities and pass them to the SQL 2005 database.
In this specific case I'm sending to the database a new blog instance (which simply consists in a name and relevant author's data) and some extra
data translated in different cultures (a collection of LocalizedData objects).
Here's an extract of my simple BlogLocalizedData class:
namespace Pacem.Providers.Blog.Logic
{
[XmlRoot("LocalizedData")]
public partial class BlogLocalizedData : ILocalizedData, IProvided<Pacem.Providers.Blog.BlogProvider>
{
/// <summary>
/// Gets or sets the title in the relevant culture.
/// </summary>
[XmlElement("Title")]
public string Title
{
get
{
return _Title;
}
set
{
_Title = value;
}
}
/// <summary>
/// Gets or sets the description in the relevant culture.
/// </summary>
[XmlElement("Description")]
public string Description
{
get
{
return _Description;
}
set
{
_Description = value;
}
}
/// <summary>
/// Gets or sets the culture of the data.
/// </summary>
[XmlElement("Culture")]
public string Culture
{
get
{
return _Culture;
}
set
{
// check if it is a valid culture:
// throws an exception if value isn't a valid culture.
CultureInfo culture = new CultureInfo(value);
_Culture = culture.Name;
}
}
}
}
Please note the custom attributes used to mark the public properties for serialization as XmlElements.
It follows the script of the stored procedure, as you can see (2nd highlighted line) and as you can read in the official documentation, I've been
telling the SQL XML Parser to parse the xml variable in a element-centric perspective.
That means the relevant data (see relevant WITH CLAUSE) must be retrieved using the content of the
XmlElements having the provided name!
There are overloaded manners to introduce hybrid logic in this sense (using both elements and attributes), I point you to the official documentation for this.
-- =============================================
-- Author: Cristian Merighi
-- =============================================
CREATE PROCEDURE [sproc_Pacem_Blog_Insert]
(
@Key varchar(64),
@AuthorFirstName nvarchar(32),
@AuthorLastName nvarchar(32),
@AuthorEmail varchar(128),
@LocalizedDataCollection xml
/*
-- sample xml:
<LocalizedDataCollection>
<LocalizedData>
<Title>title</Title>
<Description>description</Description>
<Culture>en-US</Culture>
</LocalizedData>
<LocalizedData>
<Title>titolo</Title>
<Description>descrizione</Description>
<Culture>it-IT</Culture>
</LocalizedData>
<LocalizedData>
<Title>titre</Title>
<Description>description</Description>
<Culture>fr-FR</Culture>
</LocalizedData>
</LocalizedDataCollection>
*/
, @NewID int output
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
-- inserting blog
INSERT INTO [Pacem_Blogs]
([strBlogKey]
,[strAuthorFirstName]
,[strAuthorLastName]
,[strAuthorEmail])
VALUES
(@Key
,@AuthorFirstName
,@AuthorLastName
,@AuthorEmail)
SELECT @NewID = SCOPE_IDENTITY()
-- Insert statements for procedure here
DECLARE @XmlHandle int
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @LocalizedDataCollection
INSERT INTO [Pacem_BlogsLocalized]
([IDBlog]
,[strCulture]
,[strTitle]
,[strDescription])
SELECT @NewID, [Culture], [Title], [Description]
FROM OPENXML (@XmlHandle, '//LocalizedData', 2 /* element centric */)
WITH ([Culture] varchar(8),
[Title] nvarchar(128),
[Description] nvarchar(512))
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- RAISE ERROR
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
To put together all these pieces, you just have to pass your serialized list of BlogLocalizedData as the
@LocalizedDataCollection parameter:
// serializing localizedData
XmlSerializer serializer = new XmlSerializer(
typeof(List<BlogLocalizedData>),
new XmlRootAttribute("LocalizedDataCollection"));
StringBuilder sb = new StringBuilder();
using (StringWriter tw = new StringWriter(sb))
{
XmlTextWriter xtw = new XmlTextWriter(tw);
serializer.Serialize(xtw, localizedData);
}
string xmlFragment = sb.ToString();
Take care. Bye.