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