Practical Programming

Microsoft’s excellent implementation of XML in SQL 2005 allows you to insert multiple rows with only one insert statement. Here’s how to insert many rows without calling insert many times. In this example we will have three tables in our database. They are: PRODUCTS, PRODUCTS_COLLECTIONS, PRODUCTS_COLLECTIONS_JOIN. PRODUCTS is our main table which contains our products; a product can belong to one or many collections. Therefore, we have PRODUCTS_COLLECTIONS_JOIN to bind a product to a collection via their id’s.

Pros vs. Cons

1. PRO - Only need to connect to your database once, to execute the procedure.
2. PRO - We are using XML support built into SQL 2005.
3. PRO - Keep things neat. There is only one procedure to manage which joins these tables.
4. CON - You are passing more data with the one connection (the XML). Whether this matters to you will depend on many factors dependent on your setup.

How?

SQL 2005 supports xPath strings, go here for a quick tutorial. The first statement in this procedure deletes all joins in PRODUCTS_COLLECTIONS_JOIN where product_id is equal to our product_id and the collection_id is NOT found in the XML passed. Next the procedure inserts into PRODUCTS_COLLECTIONS_JOIN where product_id is equal to our product_id, and where a collection_id is in the XML, and where a join with this product_id and collection_id does not already exist.

create procedure [spProductsCollectionsJoin] @id int, @collections xml
as
declare @deleted bit
set @deleted = 0
delete from [products_collections_join]
where
	[product_id] = @id
	and [collection_id] not in(
            select x.item.value('@id','int') as id
            from @collections.nodes('/collections/item') as x(item))
insert into [products_collections_join]([product_id],[collection_id])
	select
		[products].[id],
		[products_collections].[id]
	from [products]
	inner join [products_collections] on [products_collections].[id] in (
                   select x.item.value('@id','int') as id
                   from @collections.nodes('/collections/item') as x(item))
	left join [products_collections_join] on
                  ([products_collections_join].[collection_id] =
                   [products_collections].[id]
                   and [products_collections_join].[product_id] = @id)
	where
		[products].[id] = @id
		and [products_collections_join].[collection_id] is null
		and [products_collections].[deleted] = @deleted

To execute the join we need to create our XML to pass to the procedure:

declare @product_id int
declare @collections xml
set @product_id = 1
set @collections = '
<collections>
<item id="1"/>
<item id="2"/>
<item id="3"/>
</collections>
'
exec spProductsCollectionsJoin @product_id, @collections

After executing the procedure there will be three new rows in PRODUCTS_COLLECTIONS_JOIN which correspond to the 3 new item nodes in the XML.

Further Reading

http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

http://www.w3.org/TR/xpath

http://www.zvon.org/xxl/XPathTutorial/Output/examples.html

Source

SQL Script to create products table.
SQL Script to create products_collections table.
SQL Script to create products_collections_join table.
SQL Script to create spProductsCollectionsJoin procedure.
SQL Script to test everything.

Until next time, dream in digital.

Advanced Stuff, sql sql, xml