Introduction
This article provides basic information about how to insert one parent and multiple child records with a single stored procedure. Often developers find themselves in this situation and for this purpose many customized solutions were developed. What people tend to overlook is the fact that SQL Server provides great XML support that can be utilized for data extraction as well as serialization.
In this article a stored procedure is created requiring an XML document in nText SQL Server data format. A .NET application serializes custom objects in the format required by the stored procedure. The serialized XML data is passed to the stored procedure as any other stored procedure through ADO.NET. The stored procedure maintains transaction for Parent-Child insertion for database integrity.
A helper class can be used to take the custom object as its property and provide us with its XML representation. System.Xml.Serialization namespace provides great support for any kind of object serialization in XML format. An object representing parent table contains another object that will represent child or detail table. In the object representing child table XML serialization is used to turn the objects into desired XML format document.
Background
Northwind database is used which is normally shipped with SQL Server 2000.
In order to keep this tutorial simple i will be assuming the understanding of following from the reader.
- Familiarity with basics of XML documents and XQuery.
- Basic concepts of parent-child or master-detail table relationships.
- Familiarity with SQL Server 2000 Stored procedure.
- Familiarity with XML related stored procedure such as
sp_xml_preparedocument, sp_xml_removedocument and function Openxml. Information for these can be extracted from online help for Transact SQL accompanying the SQL Server installation.
System.Xml.Serialization namespace providing object serialization in XML format.
This article can not go into detail about above mentioned topics as they are huge enough to entire books, but brief explanation will be provided.
XML document format
Following is the format expected by our Stored Procedure.
="1.0"
<Order>
<CustomerID>ALFKI</CustomerID>
<EmployeeID>3</EmployeeID>
<OrderDate>07/3/2004</OrderDate>
<RequiredDate>07/4/2004</RequiredDate>
<ShippedDate>15/3/2004</ShippedDate>
<OrderDetails ProductID="2" UnitPrice="15"
Quantity="5" Discount="0.15"></OrderDetails>
<OrderDetails ProductID="4" UnitPrice="22"
Quantity="7" Discount="0.21"></OrderDetails>
<OrderDetails ProductID="10" UnitPrice="31"
Quantity="3" Discount="0.15"></OrderDetails>
</Order>
<CustomerID>, <EmployeeID>, <OrderDate> and <ShippedDate> are mapped to parent table Orders, where as ProductID, UnitPrice, Quantity, Discount attributes in <OrderDetails> element maps to child table [Order Details] in northwind database. The additional fields were not covered to keep the XML Document short and simple.
The decision to choose attributes over elements is purely dependent on reader's choice and the situation.
Stored Procedure
CREATE PROCEDURE xmlOrderInsert @order ntext AS
DECLARE @docHandle int, @OID int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @order
BEGIN TRANSACTION
INSERT INTO Orders( CustomerID, EmployeeID, OrderDate, RequiredDate )
SELECT CustomerID, EmployeeID, OrderDate, RequiredDate
FROM Openxml( @docHandle, '/Order', 3) WITH ( CustomerID nchar(5),
EmployeeID int, OrderDate datetime, RequiredDate datetime )
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -100 END
SET @OID = SCOPE_IDENTITY()
INSERT INTO [Order Details] ( OrderID, ProductID, UnitPrice, Quantity, Discount )
SELECT @OID AS [PO ID], ProductID, UnitPrice, Quantity, Discount
FROM OpenXml( @docHandle, '/Order/OrderDetails', 1) WITH
( ProductID int, UnitPrice money, Quantity smallint, Discount real )
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
COMMIT TRANSACTION
EXEC sp_xml_removedocument @docHandle SELECT @OID AS [Order ID]
GO
Syntax
- sp_xml_preparedocument returns a handle to the memory representation of XML document created with the second argument. This handle is later used in OPENXML function.
- OPENXML provides a rowset view over an XML document.
- sp_xml_removedocument removes the internal representation of the XML document specified by the document handle and invalidates the document handle.
First of all rowset for the parent table Order is extracted from the XML document by using Openxml( @docHandle, '/Order', 3) WITH ( CustomerID nchar(5), EmployeeID int, OrderDate datetime, RequiredDate datetime ) and inserted in the database. The primary key for the newly inserted order is then retrieved from SQL Server using SET @OID = SCOPE_IDENTITY(). then child rowsets are extracted from XML document OpenXml( @docHandle, '/Order/OrderDetails', 1) WITH ( ProductID int, UnitPrice money, Quantity smallint, Discount real ) and inserted in the database. Begin and End Transaction are used for data integerity as well as some error handling code that is self explanatory.
Classes
using System;
using System.Xml.Serialization;
namespace XMLInsert
{
public class Order
{
public Order()
{
OrderDetails=null;
}
private string CID="";
private int EID;
private string ODate;
private string RDate;
private string SDate;
[XmlElement("OrderDetails")]
public OrderDetail[] OrderDetails;
public string CustomerID
{
get
{
return this.CID;
}
set
{
CID=value;
}
}
public int EmployeeID
{
get
{
return this.EID;
}
set
{
EID=value;
}
}
public string OrderDate
{
get
{
return this.ODate;
}
set
{
ODate=value;
}
}
public string RequiredDate
{
get
{
return this.RDate;
}
set
{
RDate=value;
}
}
public string ShippedDate
{
get
{
return this.SDate;
}
set
{
SDate=value;
}
}
}
}
The above class represents parent Order table in Northwind database. The class has a property OrderDetails which is an array of the class OrderDetail which we will later define. [XmlElement("OrderDetails")] will enable each item of the array to be transformed into <OrderDetails> element when we perform object serialization.
using System;
using System.Xml.Serialization;
namespace XMLInsert
{
public class OrderDetail
{
public OrderDetail()
{
}
private int PID;
private Decimal UPrice;
private int Qty;
private Decimal disc;
[XmlAttribute]
public int ProductID
{
get
{
return this.PID;
}
set
{
PID=value;
}
}
[XmlAttribute]
public Decimal UnitPrice
{
get
{
return this.UPrice;
}
set
{
UPrice=value;
}
}
[XmlAttribute]
public int Quantity
{
get
{
return this.Qty;
}
set
{
Qty=value;
}
}
[XmlAttribute]
public Decimal Discount
{
get
{
return this.disc;
}
set
{
disc=value;
}
}
}
The above class represents child table in Northwind database. The class has four properties ProductID, UnitPrice, Quantity, Discount and each property carries [XmlAttribute] attribute before declaration. This will enable them to be transformed into XML attribute.
Using the code
First register the stored procedure in Northwind database. Just to test the stored procedure execute the stored procedure and supply the xml document provided in this tutorial as simple text format. For the sake of keeping the code simple we are only making three elements of OrderDetail array. Normally grids are used for input for details but for the sake of simplicity we are going to make only three array items for detail.
Please replace datavalues to suitable data values in your application.
Start a .Net project copy the two classes in the project and when inserting the data in the database use the following snippet
XmlSerializer serlizer = new XmlSerializer(typeof(Order));
OrderDetail[] od = new OrderDetail[3];
for(int r=0;r<3;r++)
{
}
Order o = new Order();
System.IO.MemoryStream mm=null;
try
{
mm=new System.IO.MemoryStream();
serlizer.Serialize(mm,o);
}
catch(Exception ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
mm.Close();
}
string xml = System.Text.Encoding.UTF7.GetString(mm.ToArray());
Now
