Blog ala Vidar

This is a new feature to SQL Server 2008, where you can send a table to a SP or UDF. I’ve got the question about how to handle this in 2005 many times, and I’ve now decided to post a blog about it. Before you read more about this, please consider. IS IT REALLY NECCESSARY to do this from the client? Can we do everything on the SQL Server? If so, DO IT! My following example can all be done on the server, but this is only to show you how to do it if you really need to get it from the client.

Let’s say we got a bunch of data we want to send to a SP. In this case, I’ve just built up a dataset from stbv_System_Users, where I’ve got one column, Login.

Dim vUsersDataSet As New DataSet
Dim vUsers As New afRecordSource("stbv_System_Users")

vUsers.SelectColumns.Add("Login")
vUsers.MaxRecords = 10

vUsersDataSet.Tables.Add(afAccessLayer.GetData(vUsers))

Dim vProcedure As New AppFrame3.Common.Data.afProcedureCall
vProcedure.ProcedureName = "astp_Vidar_Test1"
vProcedure.Parameters.Add("MyXML", vUsersDataSet.GetXml().ToString())

Try
	AppFrame3.Common.Data.afAccessLayer.ExecProcedure(vProcedure)
Catch ex As Exception
	AppFrame3.Win.CR3.HandleException(ex)
End Try

Here you see I’ve put it in a dataset, then used .GetXml to send it as a parameter to my SP:

CREATE PROCEDURE astp_Vidar_Test1 (@MyXML AS XML)
AS

DECLARE @DocID AS INT

EXEC sp_xml_preparedocument @DocID OUTPUT, @MyXML

SELECT *
	FROM OPENXML(@DocID, '/NewDataSet/Table1', 2)
	WITH (Login NVARCHAR(128))

EXEC sp_xml_removedocument @DocID

My SP takes one parameter, and “converts” it to SQL using OPENXML. Now I could go on, inserting these records somewhere. OPENXML takes three parameters. First one is docid, which is created by sp_xml_preparedocument. Second one is the path in the XML you want to handle. Third one is what type of input it is. If it’s with elements or attributes. 2 is elements, 1 is attributes. Under you’ll see the XML created by the .GetXml method on the dataset. I don’t care about the PrimKey field, so I’ve only specified the Login field in the WITH clause.

<NewDataSet>
  <Table1>
    <Login>vidar</Login>
    <PrimKey>63444aa2-e0b8-4c89-b476-229c81145961</PrimKey>
  </Table1>
  <Table1>
    <Login>vidar2</Login>
    <PrimKey>e72d74d0-3c8f-4256-b188-8001557d9849</PrimKey>
  </Table1>
</NewDataSet>