SQL 2005/8: Xml Type - Passing sets of data

February 12, 2010 by Christoff Truter SQL  

The following post is a bit of a follow-up to the post I did last month about TVP's in SQL 2008, what we're going to do is send a set of data to an xml parameter (introduced in SQL 2005) and insert it into a table. (Previously we sent a set of data using TVP's in 2008)

Like last month, we create a table, called friends:

CREATE TABLE [dbo].[friends](
	[friendID] [int] IDENTITY(1,1) NOT NULL,
	[firstname] [varchar](50) NOT NULL,
	[lastname] [varchar](50) NOT NULL,
 CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED 
(
	[friendID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
	IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The stored procedure is going to look something like this:

CREATE PROCEDURE [dbo].[addFriends]
	@friends XML
AS
BEGIN
	INSERT INTO friends(firstname, lastname)
	SELECT item.value('@firstname', 'varchar(50)') firstname, 
		item.value('@lastname', 'varchar(50)') lastname
	FROM @friends.nodes('//friend') t (item)	
END

Next we're going to define an xml set named friends.xml:

<?xml version="1.0" encoding="utf-8" ?>
<friends>
	<friend firstname="w" lastname="x" />
	<friend firstname="y" lastname="z" />
</friends>

Using the XML Schema Definition Tool (Xsd.exe), we generate an xsd file (XML Schema Definition), from the command prompt run: xsd friends.xml - which outputs friends.xsd.

Next we run the command xsd friends.xsd /c, which generates a C# class called friends, which we add to our project. We then add some items to our friends class, which we serialize and send to our stored procedure, observe:

using (SqlConnection connection = new SqlConnection(@"some connectionstring"))
{
	using (SqlCommand command = new SqlCommand("addFriends", connection) { CommandType = CommandType.StoredProcedure })
	{
		friends f = new friends();
		f.Items = new friendsFriend[] 
		{ 
			new friendsFriend 
			{ 
				firstname = "Jurgens", 
				lastname = "Truter" 
			},
			new friendsFriend 
			{ 
				firstname = "Roland", 
				lastname = "Cooper" 
			} 
		};

		StringWriter sw = new StringWriter();
		XmlSerializer xml = new XmlSerializer(typeof(friends));
		xml.Serialize(sw, f);

		connection.Open();
		command.Parameters.Add("@friends", SqlDbType.Xml).Value = sw.ToString();
		command.ExecuteNonQuery();
	}
}

We're not quite done yet though, you will notice that you can practically send any xml string to the stored procedure, without any exception being raised - which is a bit of a dodgy situation.

This is where our xsd file we generated previously comes into play, we define an xml schema collection in SQL, using the output from our xsd file:

CREATE XML SCHEMA COLLECTION friendCollection AS
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="friends" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="friends" msdata:IsDataSet="true" msdata:Locale="en-US">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="friend">
          <xs:complexType>
            <xs:attribute name="firstname" type="xs:string" />
            <xs:attribute name="lastname" type="xs:string" />
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Do a slight alteration to the addFriends stored procedure (notice XML(friendCollection)). You will notice that only xml that conforms to our schema, can be passed to our stored procedure - anything else will throw an exception:

ALTER PROCEDURE [dbo].[addFriends]
	@friends XML(friendCollection)
AS
BEGIN
	INSERT INTO friends(firstname, lastname)
	SELECT item.value('@firstname', 'varchar(50)') firstname, 
			item.value('@lastname', 'varchar(50)') lastname
	FROM @friends.nodes('//friend') t (item)	
END