Click here to Skip to main content
Click here to Skip to main content

Inserting XML formatted data into SQL Server 2000

, 21 Jun 2004
Rate this:
Please Sign up or sign in to vote.
This article explains Parent-Child insertion into SQL Server 2000 using XML Support provided by SQL Server 2000 and Visual Studio.NET

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.

<?xml version="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

/// <summary>
//Order.cs
/// Order class.
/// </summary>

using System;
using System.Xml.Serialization;
namespace XMLInsert
  {  
     
  public class Order
     
  {
          
  public Order()
          
  {
                 
  OrderDetails=null;  //new OrderDetail[0];
            
  }
           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
  {
  /// <summary>
  /// OrderDetails.cs
  /// Summary description for OrderDetail.
  /// </summary>
    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

//
// Prepare a seriliazer object 
      XmlSerializer serlizer = new XmlSerializer(typeof(Order));
// Declare OrderDetail array
      OrderDetail[] od = new OrderDetail[3];
      for(int r=0;r<3;r++)
      {        
        //od[r].ProductID=Convert.ToInt32(datavalues);
        //od[r].UnitPrice=Convert.ToDecimal(datavalues);
        //od[r].Quantity=Convert.ToInt32(datavalues);
        //od[r].Discount=Convert.ToDecimal(datavalues);
      }
// Declarin Order Object and setting its properties
      Order o = new Order();
      //o.CustomerID=datavalues;
      //o.EmployeeID=Convert.ToInt32(datavalues);
      //o.OrderDate=datavalues
      //o.RequiredDate=datavalues;
      //o.ShippedDate=datavalues;
      //o.OrderDetails=od;
// Declaring memory stream
      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 the variable xml can be passed to the stored procedure using ADO.NET.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

faisal abdul aziz
Web Developer
Singapore Singapore
No Biography provided

Comments and Discussions

 
Generalxml to sql Pinmemberdavecommer19-Apr-08 0:29 
GeneralInserting XML formatted data into SQL Server 200 using vb.net Pinmemberricomambo31-Oct-07 20:10 
GeneralScope_Identity gives the last ID on the my child table Pinmemberjfjavier11-May-07 13:46 
GeneralRe: Scope_Identity gives the last ID on the my child table PinmemberSukanta basak2-Mar-12 23:36 
QuestionGot error called null reference error? PinmemberTridip Bhattacharjee26-Sep-06 22:38 
GeneralError in Array PinmemberNawaz Mushwani17-Mar-06 17:23 
GeneralRe: Error in Array PinmemberJedi_pol9-Jul-06 21:29 
Questionhow to &quot;/&quot;&quot; rectify in xml Pinmembernalskumar22-Aug-05 17:11 
GeneralAbout Your Soft PinmemberWaqasButt29-Dec-04 0:21 
GeneralRe: About Your Soft PinsussAnonymous29-Dec-04 18:00 
GeneralRe: About Your Soft PinmemberWaqasButt29-Dec-04 21:20 
GeneralError converting to datetime PinmembersuckaMC18-Aug-04 5:24 
GeneralRe: Error converting to datetime PinmemberHastarin26-Oct-04 15:39 
GeneralRe: Error converting to datetime PinsussAnonymous9-Nov-04 14:08 
GeneralUseful technique Pinmemberbob bannister1-Jul-04 5:49 
GeneralRe: Useful technique Pinmemberfaisal abdul aziz6-Jul-04 19:50 
GeneralRe: Useful technique Pinmemberbob bannister6-Jul-04 20:47 
GeneralRe: Useful technique Pinmemberlishen236-Aug-04 11:29 
GeneralRe: Useful technique Pinsussricardopuente13-Apr-05 3:33 
QuestionLimits? PinmemberGanjah78622-Jun-04 12:33 
AnswerRe: Limits? Pinmemberfaisal abdul aziz28-Jun-04 20:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141029.1 | Last Updated 22 Jun 2004
Article Copyright 2004 by faisal abdul aziz
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid