|
|
I have moved to http://pentonizer.com so join me there!!!
Every once in a while people post to the lists that I am on (AspAdvice, SSWUG, 15seconds, VISBAS-L) asking about how to pass lists of data into stored procedures. There are a number of ways to accomplish this, some of which include (but not limited to):
[1] Insert into a temporary table on the client, use that in the procedure
[2] Call the stored procedure multiple times with the next value
[3] Pass in a delimited string to the procedure. Then, have a UDF or other procedure to split this into a table so set processing can be done with it.
[4] Pass in an XML string and use it as a table
There are pros and cons about each method listed above. I will focus on the fourth method.
One of the benefits to passing in an xml string of data is ease of ability to maintain a heirarchy. You can pass in multiple levels of data and multiple values for a single logical row of data as well. This offers much flexibility to the developer in terms of passing data to a SQL Server database. I'll focus on SLQ Server 2000 (there are much more XML capabilities in Yukon). So, let's start with some basics. You can have element-centric documents, attribute-centric documents, and mixed. Here are examples of that:
Element-centric data:
<messages><message>This is a message<po>123454321</po></message></messages> Attribute-Centric data:
<messages><message text=“This is a message” po=“123454321” /></messages>
Mixed data:
<messages><message text=“This is a message” po=“123454321”>Here is inner text</message></messages>
SQL Server can use any of these types, but I tend to use Attribute-centric data more often, mostly for the smaller payload. These can come into a stored procedure with any of the character datatypes (char/varchar, nchar/nvarchar, text/ntext) I typically use TEXT because I don't deal with National Text (extended character sets) very often. And, since TEXT has an uppe rlimit of 2GB of data, I am pretty confident that I will not overflow with whatever I send. Now, let's look at the methods we would use to utilize this xml in SQL Server. When this data is passed into a stored procedure we have to tell SQL Server that this is XML. We use sp_xml_preparedocument for that. sp_xml_preparedocument is used to associate some text [that is in XML format] in a node tree that can be XPath searched. It can then be binded to a rowset provider [such as OPENXML] and be treated like any other table or view. With this binding, we must be able to deallocate it. sp_xml_removedocument is used to deallocate an xml node tree. This is vital to SQL Server as neglecting to release resources can lead to memory leaks. The main construct in SQL Server ot use tihs isthe OPENXML clause in a query:
OPENXML(
idoc int [in], rowpattern nvarchar[in], [flags byte[in]])
[WITH (SchemaDeclaration | TableName)]
idoc is a handle to an XML document that was prepared with sp_xml_preparedocument
rowpattern is an XPath string that tells OPENXML where to start or search
flags is a bitmask value that tells OPENXML to use element or attribute centric mapping
SchemaDeclaration is a table layout mapped to SQL data types
TableName is an existing table that OPENXML can use to map elements/attributes directly to columns by name
The rowpattern is a typical XPath query:
/messages/message/segment
/messages/message/segment[@unit_cost>5.0]
This is an NVARCHAR string and can be assigned programmatically in your stored procedures. Flags are used to define the mapping to the relation:
[] 0: Defaults to attribute-centric mapping
[] 1: Use attribute-centric mapping
[] 2: Use element-centric mapping
[] 8: Can combine element and attribute centric mappings (deals with @mp:xmltext as well)
Even if you are using flags 1 or 2 you can still access values across those boundaries (i.e. use attribute-centric mapping and still access element values) but there is a performance penalty. SchemaDeclaration or TableName: This tells OPENXML the name, type and location of data it is using. Using the TableName is much easier, but it is not flexible. SchemaDeclarations are much more versatile.
TableName:
SELECT * FROM OPENXML(@idoc, ‘/ROOT’, 1)
WITH dbo.MyTable
SchemaDeclaration:
SELECT a.* FROM OPENXML(@idoc, ‘/ROOT’, 1)
WITH (
colA varchar(10) ‘@myAttributeA’
, colB int ‘@myAttributeB’
) AS a
More information about SchemaDeclarations
Be sure that you have the right datatypes defined. Conversions are not always automatic. For instance, ‘’ (a zero-length string) will implicitly convert to an integer but will not implicitly convert to a decimal/numeric. If your data is variable (as in it could be a ZLS) then you can set your ColumnPattern to a VARCHAR and then manage your conversions in the SELECT clause.
Let's look at some basic code for this (leaving out error code for simplicity):
StreamReader sr = new StreamReader(fileName);
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("return", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("@xml", SqlDbType.Text).Value = sr.ReadToEnd();
sr.Close();
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
conn.Dispose();
The XML we want to pass in:
<?xml version="1.0"?>
<messages currentDate="2003-09-08T15:40:31">
<message po="100000006980" version_nbr="1" not_before_date="2003-09-11">
<segment item="5234004210108" unit_cost="6.25" qty="6" />
<segment item="5234004210109" unit_cost="26.25" qty="6" />
<segment item="5234004210110" unit_cost="16.25" qty="6" />
<segment item="5234004210111" unit_cost="26.25" qty="6" />
<segment item="5234004210112" unit_cost="26.25" qty="6" />
<segment item="5234004210113" unit_cost="26.25" qty="6" />
</message>
<message po="100000006982" version_nbr="1" not_before_date="2003-09-29">
<segment item="5238052010705" unit_cost="145.40" qty="4" />
<segment item="5238052010706" unit_cost="145.40" qty="4" />
<segment item="5238053010605" unit_cost="15.40" qty="20" />
<segment item="5238053010606" unit_cost="15.40" qty="20" />
</message>
</messages>
And the SQL:
CREATE PROCEDURE dbo.MyProcedure
@xml TEXT
AS
SET NOCOUNT ON
DECLARE @ret int, @iXml int
EXECUTE @ret = [master].[dbo].[sp_xml_preparedocument] @iXml OUTPUT, @xml
INSERT INTO [dbo].[XmlTestTable]
SELECT
a.po, a.version_nbr, a.not_before_date, a.item, a.unit_cost, a.qty
FROM
OPENXML(@iXml, '/messages/message/segment', 1) -- start at the message segment node
WITH (
po decimal(12, 0) '../@po'
, version_nbr int '../@version_nbr'
, not_before_date datetime '../@not_before_date'
, item char(13)
, unit_cost decimal(8, 2)
, qty int
) AS a
-- release the XML document now that we are done
EXECUTE [master].[dbo].[sp_xml_removedocument] @iXml
GO
There is quite a bit more of this. I'll post more later in the week.
Anonymous comments are disabled
|
|
|