Click here to Skip to main content
13,552,926 members
Click here to Skip to main content
Add your own
alternative version


40 bookmarked
Posted 14 Apr 2004

Saving Master-Details form using XML

, 14 Apr 2004
Rate this:
Please Sign up or sign in to vote.
This article shows how to save a master-details form using XML.


One of the most common types of form in database applications is the "Master-Details" form. One of the ways to save this kind of form is to loop through the "Detail" sections and insert the values in the table. A better way would be to send all the values to SQL server in one shot and then insert all of them in the table. This is exactly what my code does.

Using the code

As an example, I have used two tables Purchase and PurchaseDetails; the schema of the two tables is as follows:

    InvoiceID numeric(18, 0) NOT NULL IDENTITY (1, 1),
    InvoiceDate datetime NOT NULL,
    SupplierName varchar(50) NULL
CREATE TABLE PurchaseDetails (
    InvoiceID int NOT NULL ,
    [Description] varchar (75) NOT NULL ,
    Quantity int NOT NULL ,
    Rate numeric(18, 2) NOT NULL , 
    Amount numeric(18, 2) NOT NULL

Next is the stored procedure. The stored procedure has to accept the XML string which can be any string data type. These are the steps to be followed in the stored proc:

  1. Insert the values in the Master table, in this case Purchase.
  2. exec sp_xml_preparedocument to create the XML document of the XML that you have passed.
  3. Using OPENXML, insert the values in the Details table, in this case PurchaseDetails.
  4. Last but not the least, clean up the XML document using sp_xml_removedocument.

All of the functions are very well documented in the MSDN, so will not explain then here again. The code in the stored procedure is as follows:

CREATE PROC InsertPurchaseOrder (@InvoiceDate DATETIME, 
          @SupplierName VARCHAR(50), @PurchaseDetails NTEXT)
     DECLARE @InvoiceID INT
     DECLARE @idoc INT

    INSERT INTO Purchase( InvoiceDate, SupplierName)
     VALUES (@InvoiceDate, @SupplierName)

     SET @InvoiceID = @@IDENTITY

    -- Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @PurchaseDetails
    -- Insert the values in the details table
    INSERT PurchaseDetails (InvoiceID, [Description], Quantity, Rate, Amount)
    SELECT @InvoiceID, [Description], Quantity, Rate, Amount
    FROM OPENXML (@idoc, '/PurchaseOrder/Details', 2)
    WITH PurchaseDetails
    -- Destory the internal representation of the XML document.
    EXEC sp_xml_removedocument @idoc

In the form, I have created a DataTable to hold the details of the purchase order. Add this DataTable to a DataSet and convert it into XML form.

 private void CreateDT () {
    // Name is important this will be used in the stored proc 
    dtProd = new DataTable("Details"); 

    // Create the table structure 
       Type.GetType("System.String")).DefaultValue = ""; 
       Type.GetType("System.Int32")).DefaultValue = 0; 
       Type.GetType("System.Decimal")).DefaultValue = 0.0; 
    dtProd.Columns.Add("Amount", Type.GetType("System.Decimal"), 
                                  "Rate * Quantity").ReadOnly = true;

private void btnSave_Click(object sender, System.EventArgs e) {
    DetailsXML = ds.GetXml();

Points to remember

  1. Run the SQL script that is included with this article.
  2. The column names of the DataTable should be the same as in the database.
  3. DataSet name and the DataTable name has to match with names used in the stored proc.

Additional Information

I have used Microsoft's Data Access Application Block for executing the stored proc. You can find the code and documentation here.


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


About the Author

Muffadal Jaorawala
Web Developer
United Arab Emirates United Arab Emirates
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralStill a great piece till now Pin
chanmm30-Oct-17 17:03
memberchanmm30-Oct-17 17:03 
Generalmerci Pin
d4brain26-Mar-07 22:44
memberd4brain26-Mar-07 22:44 
Daniel Stephen Rule15-Apr-04 7:46
memberDaniel Stephen Rule15-Apr-04 7:46 
Muffadal Jaorawala15-Apr-04 10:21
memberMuffadal Jaorawala15-Apr-04 10:21 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180515.1 | Last Updated 15 Apr 2004
Article Copyright 2004 by Muffadal Jaorawala
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid