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, the management of batch operations in a database, which significantly reduces the need for more than one round-trip to a database. OpenXML is a Transact-SQL statement that allows the representation of 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 an owner and name.

CREATE PROCEDURE dbo.dspSample

Receive an XML string.

@doc varchar(8000)
AS

Declare an 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
BEGIN
    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;
END
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 += 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
{
    Connection = sqlConn,
    CommandType = CommandType.StoredProcedure,
    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.