SQL 2005/8: Xml Type - Passing sets of data
Source Code
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>
,
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 = "Julie",
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
Posted by - Christoff Truter
Date - 2010-02-12 19:04:23
Post comment