Sunday, September 14, 2008

use XML and OPENXML to perform Data Manipulation : Table Variable as a parameter to stored procedure

There might be times where you want to perform operations on multiple rows with a single call to the database using a stored procedure.But the problem is SQL Server has no table parameter that can take a collection of rows.To solve this problem u can pass all the rows in a string variable, parse the string into columns and rows and perform the operation you want.Sometimes you might endup sending the delimeter in the data that can cause the parsing to break.A cleaner solution is to pass the xml string and use open xml to transform string data into rows.

Following function is used to build the required xml string for the strored procedure.

Private Shared Function BuildCustomerXMLString(ByVal cc As CustomerCollection) As String

Dim ms As New MemoryStreamDim sr As StreamReader

Dim XMLString As String

Dim writer As New XmlTextWriter(ms, System.Text.Encoding.Unicode)

writer.Formatting = Formatting.Indented

writer.WriteStartElement("ROOT")

For Each lcc As Customers In cc
writer.WriteStartElement("Customer")
writer.WriteAttributeString("CustomerID", XmlConvert.ToString(lcc.CustomerID))
writer.WriteAttributeString("CustomerName", lcc.CustomerName)
writer.WriteAttributeString("CustomerEmail", lcc.CustomerEmail)
writer.WriteEndElement()
End If
Next
''End for root

writer.WriteEndElement()

''commit and close the writer

writer.Flush()ms.Position = 0

''create the stream from the Memory Stream

sr = New StreamReader(ms)''Get the string from the stream

XMLString = sr.ReadToEnd()

Return XMLString

End Function

'''''''''''''''''''''''

Following strored procedure is used to update the customer information using the openxml.


CREATE PROCEDURE [dbo].[uspUpdateCustomerInfo]
(
@CustomerInfo varchar(max)
)
AS
SET NOCOUNT ON;
DECLARE @idoc int;
BEGIN TRY
DECLARE @InputTable table (
CustomerID uniqueidentifier
,CustomerName varchar(50)
,CustomerEmail varchar(50)
);
-- CREATE A HANDLE (iDoc) FOR THE XML DOCUMENT
-- BECAUSE THE OPENXML() STATEMENT NEEDS IT.
--
EXEC sp_xml_preparedocument @idoc OUTPUT, @CustomerInfo;
INSERT @InputTable (
CustomerID
,CustomerName
,CustomerEmail
)
SELECT
CustomerID
,CustomerName
,CustomerEmail
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (
CustomerID uniqueidentifier
,CustomerName varchar(50)
,CustomerEmail varchar(50)
) c;

UPDATE c
SET
CustomerID = it.CustomerID
,CustomerName = it.CustomerName
,CustomerEmail = it.CustomerEmail
FROM Customers c
INNER JOIN @InputTable it ON it.CustomerID = c.CustomerID;
GO

No comments:

Post a Comment