Most developers have faced the challenge of passing arrays of data inside a SQL Server Database by using Stored Procedures.
I have seen other implementations to solve this problem, like using comma separated values, but from my point of view using XML is by far the best way, as it allows to pass arrays of data and complex structures at the same time.
I'll start with a simple example where we want to send to a SQL Server Database the following data:
ID 1
Name Albert Einstein
Email aeinstein@amazingpeople.com
ID 2
Name Salvador Dali
Email sdali@amazingpeople.com
The first step is of course to build the stored procedure to process our XML data.
For demonstration purposes we'll just select the data and display it. On a real scenario you might want to execute some other (CRUD) operations.
In the code bellow I'll use the OPENXML and a cursor to make things a litle bit more interesting:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ricardo Matos http://matosdotnet.com/
-- Create date:
-- Description: Demonstration of the OpenXML feature
-- =============================================
CREATE PROCEDURE [dbo].[SP_ProcessXML_People]
-- Add the parameters for the stored procedure here
@xmlDocument xml
AS
BEGIN
SET NOCOUNT ON;
DECLARE @docHandle int;
DECLARE @ID nvarchar(20);
DECLARE @Name nvarchar(50);
DECLARE @Email nvarchar(50);
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
DECLARE cusor_XMLCursor CURSOR FOR
SELECT
[ID],
[Name],
[Email]
FROM OPENXML(@docHandle, N'/PEOPLE/PEOPLE',1)
WITH (
[ID] nvarchar(20) 'ID'
,[Name] nvarchar(50) 'Name'
,[Email] nvarchar(50) 'Email'
)
VENDORS;
----
OPEN cusor_XMLCursor;
--
FETCH NEXT FROM cusor_XMLCursor INTO
@ID, @Name, @Email;
--
WHILE @@FETCH_STATUS = 0
BEGIN
--
-- Do Inserts , Updates Or Deletes Here...
-- For Demonstration purposes I will simply do a SELECT
SELECT @ID, @Name,@Email;
--
FETCH NEXT FROM cusor_XMLCursor INTO
@ID, @Name, @Email;
END
--
CLOSE cusor_XMLCursor;
DEALLOCATE cusor_XMLCursor;
EXEC sp_xml_removedocument @docHandle;
RETURN;
END
Now we can test our stored procedure by using some real XML and see if it works properly:
DECLARE @RC int
DECLARE @xmlDocument xml
-- TODO: Set parameter values here.
SET @xmlDocument = N'<?xml version="1.0" standalone="yes"?>
<PEOPLE>
<PEOPLE>
<ID>1</ID>
<Name>Albert Einstein</Name>
<Email>aeinstein@amazingpeople.com</Email>
</PEOPLE>
<PEOPLE>
<ID>2</ID>
<Name>Salvador Dali</Name>
<Email>sdali@amazingpeople.com</Email>
</PEOPLE>
</PEOPLE>';
EXECUTE @RC = [Ricardo].[dbo].[SP_ProcessXML_People]
@xmlDocument
The result should be the following:
OK, we got this far but how to use it from within our application?The only requirement is to call the stored procedure and pass a string with the XML content.
We could use Linq To XMl to create the required XML and pass the result to the stored procedure, but for compatibility with older versions of the .NET framework I'll just build an example based on the ADO.NET model, so here it goes in C#:
DataSet ds = new DataSet("PEOPLE");
DataTable dt = new DataTable("PEOPLE");
string _ID = "ID";
string _Name = "Name";
string _Email = "Email";
dt.Columns.Add(new DataColumn(_ID,Type.GetType("System.Int32")));
dt.Columns.Add(new DataColumn(_Name, Type.GetType("System.String")));
dt.Columns.Add(new DataColumn(_Email, Type.GetType("System.String")));
ds.Tables.Add(dt);
DataRow dr = dt.NewRow();
dr[_ID] = 1;
dr[_Name] = "Albert Einstein";
dr[_Email] = "aeinstein@amazingpeople.com";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[_ID] = 2;
dr[_Name] = "Salvador Dali";
dr[_Email] = "sdali@amazingpeople.com";
dt.Rows.Add(dr);
CallStoredProcedure_SP_ProcessXML_People(ds.GetXml());
(*) I have not posted the definition of the function because it is out of the scope of this article.
At the end we call the function CallStoredProcedure_SP_ProcessXML_People with the one argument which is the XML content as a string.
That's all it is needed...
Cheers
