Insert Master-Detail Data using Transact-SQL

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

1. Open Microsoft SQL Server 2000 Query Analyzer.
   
2. In the text panel, define the affected database.

   [TSQL]
   USE MYDATABASE

3. Create the procedure and assign a owner and name.

   CREATE PROCEDURE dbo.dspSample

4. Receive an XML string.

   @doc varchar(8000)
   AS

5. Declare a XML document handle.

   DECLARE @hdoc int

6. Generate the document in memory.

   EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

7. Create a new transaction.

   BEGIN TRANSACTION

8. 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

9. 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

10. 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

11. Commit the transaction.

    COMMIT

12. Remove the XML document from memory.

    EXEC sp_xml_removedocument @hdoc

    Note Skipping this step avoids freeing memory and will result in poor performance. 

13. 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.

[C#]

XmlDocument xmldoc = new XmlDocument();
XmlElement doc = xmldoc.CreateElement("doc");
xmldoc.AppendChild(doc);
XmlElement publisher = xmldoc.CreateElement("publisher");
doc.AppendChild(publisher);
string pub_id = "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");
for (int i = 1; i < 4; i++)
{
XmlElement title = xmldoc.CreateElement("title");
doc.AppendChild(title);
StringBuilder titleID = new StringBuilder("DT100");
StringBuilder titleName = 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");
}

[Visual Basic]

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.

[C#]

string connS = "data source=(local);database=pubs;integrated security=SSPI;persist security info=false";
SqlConnection sqlConn = new SqlConnection(connS);
sqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dspInsertPublisher_and_Titles";
cmd.Parameters.AddWithValue("@doc", xmldoc.OuterXml);
cmd.ExecuteNonQuery();
sqlConn.Close();

[Visual Basic]

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