Introduction
Many applications have components that connect, manage, and consume data from a database. If you are designing/building an application that connects to a database to continuously insert, update or delete data, you should keep in mind that each operation will need a round-trip to the database and will consume valuable resources (e.g., network traffic, memory, CPU, etc.). Microsoft SQL Server 2000 SQLXML allows among other things to manage batch operations in a database, which reduces significantly the need of more than one round-trip to a database. OpenXML is a Transact-SQL statement that allows to represent data in XML format and can be used to insert, update, and delete more than one row (represented by an element) in a table or group of tables.
Note: The samples provided in this document use the PUBS database shipped with Microsoft SQL Server 2000.
To create a stored procedure using SQLXML - OPENXML
- Open Microsoft SQL Server 2000 Query Analyzer.
- In the text panel, define the affected database.
[TSQL]
USE MYDATABASE -
Create the procedure and assign a owner and name.
CREATE PROCEDURE
dbo.dspSample -
Receive an XML string.
@doc
varchar(8000)
AS -
Declare a XML document handle.
DECLARE
@hdoc int -
Generate the document in memory.
EXEC
sp_xml_preparedocument @hdoc OUTPUT, @doc -
Create a new transaction.
BEGIN TRANSACTION
-
If you need to insert rows to a table, use the following sample code and replace the table and field names with the ones you need:
INSERT INTO
MYTABLE
SELECT *
FROM OPENXML(@hdoc, 'XPath query')
WITH MYTABLE -
If you need to update rows to a table, use the following sample code and replace the table and field names with the ones you need:
UPDATE
MYTABLE
SET
MYTABLE.fieldX = XMLTABLE.fieldX,
MYTABLE.fieldY = XMLTABLE.fieldY
FROM OPENXML(@hDoc, 'XPath query')
WITH MYTABLE XMLTABLE
WHERE MYTABLE.fieldID = XMLTABLE.fieldID -
If you need to delete rows to a table, use the following sample code and replace the table and field names with the ones you need:
DELETE
MYTABLE
FROM OPENXML(@hDoc, 'XPath query')
WITH MYTABLE XMLTABLE
WHERE MYTABLE.fieldID = XMLTABLE.fieldID -
Commit the transaction.
COMMIT
-
Remove the XML document from memory.
EXEC
sp_xml_removedocument @hdoc Note Skipping this step avoids freeing memory and will result in poor performance.
-
Finish and run the procedure.
RETURN
GO
The following sample code shows how to create a stored procedure to insert a publisher and its corresponding titles (master-detail relationship) to the PUBS database: [TSQL]
CREATE PROCEDURE dbo.dspInsertPublisher_and_Titles
@doc varchar(8000)
AS
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
BEGIN TRANSACTION
INSERT INTO PUBLISHERS
SELECT * FROM OPENXML(@hdoc, '//publisher')
WITH PUBLISHERS
INSERT INTO TITLES
SELECT * FROM OPENXML(@hdoc, '//title')
WITH TITLES
COMMIT
EXEC sp_xml_removedocument @hdoc
RETURN
GO
To generate an XML document from your .NET application.
Create an XML document with an element for each row you need to insert and the corresponding attribute values. The following example shows how to create an XML document from a .NET application.
Dim xmldoc As XmlDocument = New XmlDocument
Dim doc As XmlElement = xmldoc.CreateElement("doc")
xmldoc.AppendChild(doc)
Dim publisher As XmlElement = xmldoc.CreateElement("publisher")
doc.AppendChild(publisher)
Dim pub_id As String = "9919"
publisher.SetAttribute("pub_id", pub_id)
publisher.SetAttribute("pub_name", "DotNetTreats Books")
publisher.SetAttribute("city", "Redmond")
publisher.SetAttribute("state", "WA")
publisher.SetAttribute("country", "USA")
Dim i As Integer = 1
Do While (i < 4)
Dim title As XmlElement = xmldoc.CreateElement("title")
doc.AppendChild(title)
Dim titleID As StringBuilder = New StringBuilder("DT100")
Dim titleName As StringBuilder = New StringBuilder("OOP Concepts and .NET Part ")
title.SetAttribute("title_id", titleID.Append(i).ToString)
title.SetAttribute("title", titleName.Append(i).ToString)
title.SetAttribute("type", "Technical Article")
title.SetAttribute("pub_id", pub_id)
title.SetAttribute("price", "19.9900")
title.SetAttribute("advance", "9000.0000")
title.SetAttribute("royalty", "10")
title.SetAttribute("ytd_sales", "1000")
title.SetAttribute("notes", "Object-Oriented Programming concepts and samples.")title.SetAttribute("pubdate", "2005-01-30")
i = (i + 1)
Loop
To insert data to the corresponding database using ADO.NET
Create a connection and a command that will call the stored procedure and send the XML document as a parameter.
Dim connS As String = "data source=(local);database=pubs;integrated security=SSPI;persist security info=false"
Dim sqlConn As SqlConnection = New SqlConnection(connS)
sqlConn.Open()
Dim cmd As SqlCommand = New SqlCommand
cmd.Connection = sqlConn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dspInsertPublisher_and_Titles"
cmd.Parameters.Add("@doc", xmldoc.OuterXml)
cmd.ExecuteNonQuery()
sqlConn.Close()
Note: The sample source code* for this document works only in Visual Studio 2005
NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# CORNER (WWW.C-SHARPCORNER.COM).