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
For Each lcc As Customers In cc
''End for root
''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()
Following strored procedure is used to update the customer information using the openxml.
CREATE PROCEDURE [dbo].[uspUpdateCustomerInfo]
SET NOCOUNT ON;
DECLARE @idoc int;
DECLARE @InputTable table (
-- CREATE A HANDLE (iDoc) FOR THE XML DOCUMENT
-- BECAUSE THE OPENXML() STATEMENT NEEDS IT.
EXEC sp_xml_preparedocument @idoc OUTPUT, @CustomerInfo;
INSERT @InputTable (
FROM OPENXML (@idoc, '/ROOT/Customer',1)
CustomerID = it.CustomerID
,CustomerName = it.CustomerName
,CustomerEmail = it.CustomerEmail
FROM Customers c
INNER JOIN @InputTable it ON it.CustomerID = c.CustomerID;
Sunday, September 14, 2008
use XML and OPENXML to perform Data Manipulation : Table Variable as a parameter to stored procedure
IIS Manager Crashes on Start Windows 7, IIS 7.5, Power Shell 5.1. Here is the error I got in the event viewer. IISMANAGER_CRASH IIS Ma...
Update: 1/27/2013 I have got a very good response and feedback for the tool, I am working on the new version of it and soon will be relea...
If you have played with or is using System.DirectoryServices.ActiveDirectory, you might jump into the consclusio that "DirectorySearche...
A small utility written in c# to help get object information in SQL Server Management Studio quickly and easily. It can be used on any versi...