Insert Data into a SQL table using a1 XML File

This article provides basic information about how to insert multiple 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.

XML document format

Following is the format expected by our Stored Procedure.

<?xml version="1.0" standalone="true"?>
-<Root> -
<Users>
 <UserID>1</UserID>
<UserName>Admin</UserName> <Password>AQBM8/mZQUM6EHttKRM01gN1DehPz3X1WUU=</Password>
 <CreatedOn>2007-12-23T17:36:00-08:00</CreatedOn>
 <ModifiedOn>2010-09-06T09:30:00-07:00</ModifiedOn>
 <Status>true</Status>
 <UserType>0</UserType>
 <EmailID>[email protected]</EmailID>
<EmployeeID>1</EmployeeID>
 <IsDeleted>false</IsDeleted>
 <CreatedBy>1</CreatedBy>
 <ModifiedBy>1</ModifiedBy>
 <Remarks/>
<DisplayName>Administrator</DisplayName>
<ContactNo>9762212400</ContactNo>
 <Web>true</Web>
 <Application>true</Application>
<ChangedPWD>true</ChangedPWD>
 <DOB>2000-05-19T00:00:00-07:00</DOB>
</Users>
</Root>

The decision to choose attributes over elements is purely dependent on the reader's choice and the situation.

Create a table in following format

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](10) NOT NULL,
    [Password] [varbinary](max) NULL,
    [CreatedOn] [smalldatetime] NULL,
    [ModifiedOn] [smalldatetime] NULL,
    [Status] [bit] NULL,
    [UserType] [int] NULL,
    [EmailID] [varchar](50) NULL,
    [EmployeeID] [bigint] NULL,
    [IsDeleted] [bit] NULL,
    [CreatedBy] [bigint] NULL,
    [ModifiedBy] [bigint] NULL,
    [Remarks] [varchar](150) NULL,
    [DisplayName] [varchar](50) NULL,
    [ContactNo] [varchar](15) NULL,
    [Web] [bit] NULL,
    [Application] [bit] NULL,
    [ChangedPWD] [bit] NULL,
    [DOB] [smalldatetime] NULL,
    [UserGroupID] [int] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
    [UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Stored Procedure

/****** Object:  StoredProcedure [dbo].[MyInsertSP]    Script Date: 10/21/2011 14:43:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER  PROCEDURE [dbo].[MyInsertSP]
(
            @strXML text
)
AS
      Declare @intPointer int
      exec sp_xml_preparedocument @intPointer output, @strXML

      Insert into Test.dbo.Users
      Select      *
      from OpenXml(@intPointer,'/Root/Users',2)
      With (UserName varchar(10) 'UserName', Password varbinary(max) 'Password', CreatedOn smalldatetime 'CreatedOn',
  ModifiedOn smalldatetime 'ModifiedOn', Status bit 'Status', UserType int 'UserType', EmailID varchar(50) 'EmailID', EmployeeID bigint 'EmployeeID', IsDeleted bit 'IsDeleted',
  CreatedBy bigint 'CreatedBy', ModifiedBy bigint 'ModifiedBy', Remarks varchar(150) 'Remarks', DisplayName varchar(50) 'DisplayName', ContactNo varchar(15) 'ContactNo',
  Web bit 'Web', Application bit 'Application', ChangedPWD bit 'ChangedPWD', DOB smalldatetime 'DOB', UserGroupID int 'UserGroupID')

      exec sp_xml_removedocument @intPointer

RETURN

Classes

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.ComponentModel;
using System.Data;
public partial class Users
{
    #region SEARCH
    [DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
    public static DataSet GetSearch(System.Int32? userID, System.String userName, System.String password, System.Boolean? changedPWD, System.DateTime? dob, System.Int32? userType, System.Boolean? status, System.Boolean? IsDeleted)
    {
        Database db;
        string sqlCommand;
        DbCommand dbCommand;

        db = DatabaseFactory.CreateDatabase();
        sqlCommand = "[dbo].gspUsers_SEARCH";
        dbCommand = db.GetStoredProcCommand(sqlCommand, userID, userName, password, changedPWD, dob, userType, status, IsDeleted);

        return db.ExecuteDataSet(dbCommand);
    }  

    #endregion

}

The above class represents parent Order table in User database. The class has a property Users which is an array of the class Users which we will later define. [XmlElement("Users")] will enable each item of the array to be transformed into <Users> element when we perform object serialization.

Using the code

First register the stored procedure in the Test database. Just to test the stored procedure execute the stored procedure and supply the xml document provided in this tutorial as simple text. For the sake of keeping the code simple, we are only making three elements of the Users Dataset. 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 the data values to suitable data values in your application.

Start .Net projects copy the two classes in the project and when inserting the data in the database use the following snippet

 StringWriter swReq = new StringWriter();
        dt.WriteXml(swReq);

        string strXML = swReq.ToString();
       // string strXML = swReq.ToString();
        //Pass this XML string as an argument to a Stored Procedure as given in the following Code Snippet:
        SqlConnection sCn = new SqlConnection("server=datasourse;uid=sa;pwd=pwd;database=Test");
        sCn.Open();
        //SqlCommand sCmdDel = new SqlCommand("Delete from publishers where pub_id='9986'", sCn);
        //sCmdDel.ExecuteNonQuery();
        SqlCommand sCmd = new SqlCommand("MyInsertSP", sCn);
        sCmd.CommandType = CommandType.StoredProcedure;
        // Set up Parameter for Stored Procedure
        sCmd.Parameters.Add("@strXML", SqlDbType.VarChar).Value = strXML;
        sCmd.ExecuteNonQuery();

        Console.WriteLine("Data Inserted Successfully");
        Console.Read();
        sCn.Close();
        sCn = null;

Now the variable xml can be passed to the stored procedure using ADO.NET.