Introduction

We have all faced the problem while designing our applications using MS SQL Server 2005 where we want to send multiple rows to be modified (to be inserted or updated or deleted) to the database. We know how to return a set of rows from the database to the application, but we do not have any feature until SQL Server 2005 to send multiple rows to the database.

In the previous article of this series, Sending Multiple rows to the Database from an application - Part I, we already discussed how we can send a delimited string to the database for updating multiple rows. In this article I will show you how you can parse an XML and convert it into a table format.

We will create an XML string at the application layer and send it to the SP, where we parse that XML and modify the records. Here we don't need to parse the XML explicitly, but SQL Server 2005 will do it for us, when we use XQuery functions.

Sending XML data to SP

When we create an XML data, we have two different methods to put data in to the XML:

• Attribute Based
• Element Based

Parsing Attribute Based XML

In this, we put our target data in the attribute, which is enclosed by specific elements. Basically we need to create hierarchical data in XML format.

Let's consider that we want to send the name and salary of multiple employees to the database. We create an XML document like the below:

 Declare @xml XML
 SET @xml = N'<ROWS>
 <ROW Name="Richard" Salary="1100"/>
 <ROW Name="Cliff" Salary="1200"/>
 <ROW Name="Donna" Salary="13000"/>
 <ROW Name="Ann" Salary="1500"/>
 </ROWS>'

 SELECT T.Item.value('@Name', 'VARCHAR(50)') Name,
        T.Item.value('@Salary', 'INT') Salary
 FROM @xml.nodes('/ROWS/ROW') AS T(Item)

First, I would like to explain the different XQuery functions, which I have used to get the data from the above XML and used it in the stored procedure (SP). In the XML variable, my top level element is ROWS, which has multiple ROW tags that further contain the actual data tags e.g. Name and Salary as attributes.

Looking at the FROM clause of the SQL, it has a nodes() function taking the target element path starting from root element, from where data is to be read. The nodes() function returns a row set that contains logical copies of the XML data. Using the value() function, you can retrieve multiple values from the row set. The value() function takes two parameters: the first would be the attribute name prefixed by "@" and the second would be the SQL Server data type to which to convert that value. We need the data type conversion because all attribute values are considered as strings.

For the purposes of this illustration I have used the table below, wherein we need to insert multiple records sent by the application layer.

--Creating a base table
CREATE TABLE Emp(
 ID INT IDENTITY (1, 1),
 Name VARCHAR(50),
 Salary INT
);

SP Implementation: parsing attribute based XML

The SP below reads data from the attribute based XML document and inserts into our base table (Emp).

CREATE PROCEDURE [dbo].[InsertEmp2]
(
 @xml XML
)
AS
BEGIN
 INSERT INTO Emp (Name, Salary)
 SELECT T.Item.value('@Name', 'VARCHAR(50)') Name,
        T.Item.value('@Salary', 'INT') Salary
 FROM @xml.nodes('/ROWS/ROW') AS T(Item)
RETURN 0
END

Note: XQuery functions are case sensitive.

SP Implementation: parsing attribute based XML: Usage

Here I am creating an XML variable and populating the xml variable with valid XML having elements and attributes, which I am passing to the SP. If you do not have any attribute value, then you can omit whole attribute, which will return NULL. Make sure that your target table accepts NULL in that column.

Declare @x XML
SET @x = N'<ROWS>
<ROW Name="Richard" Salary="1100"/>
<ROW Name="Cliff" Salary="1200"/>
<ROW Name="Donna" Salary="13000"/>
<ROW Name="Ann" Salary="1500"/>
</ROWS>'
EXEC InsertEmp2 @x
SELECT * FROM Emp

Parsing Element Based XML

In this, we put our target data under elements. Basically we need to create a hierarchical data in XML format. If we wanted to send name and salary of multiple employees, we create XML like below:

Declare @xml XML
 SET @xml = N'<ROWS>
<ROW> 
<Name>Tom</Name> 
<Salary>2100</Salary> 
</ROW>
<ROW> 
<Name>Nuk</Name> 
<Salary>2200</Salary> 
</ROW>
<ROW> 
<Name>Gilbert</Name> 
<Salary>2300</Salary> 
</ROW>
<ROW> 
<Name>Mat</Name> 
<Salary>2600</Salary> 
</ROW>
</ROWS>'

Here each ROW element is designated as the record of an employee, containing name and salary. These rows are enclosed by a top level element called ROW. Each ROW element contains two elements name and salary respectively. Further these elements (name and salary) have data between start and end element XML tags.

 SELECT T.Item.query('./Name').value('.', 'VARCHAR(50)') Name,
        T.Item.query('./Salary').value('.', 'INT') Salary
 FROM @xml.nodes('/ROWS/ROW') AS T(Item)

Earlier we talked about the nodes() and value() XQuery functions. Now, I would like to explain about the query() function, which is used here. The query() function takes one XQuery expression as a parameter (in our case './Name' or './Salary') and returns the specified XML element in XML format like:

query('./Name')

would return

<Name>......</Name>

from the rowset.

But if you look at value() function.

value('.', 'VARCHAR(50)')

it has a dot "." as the first parameter, which specifies that read the value of current element and second parameter would convert the read value into specified SQL Server data type.

SP Implementation: parsing element based XML

The SP belwo reads data from an element based XML and inserts into our base table (Emp).

CREATE PROCEDURE InsertEmp3
(
 @xml XML
)
AS
BEGIN
 INSERT INTO Emp (Name, Salary)
 SELECT T.Item.query('./Name').value('.', 'VARCHAR(50)') Name,
        T.Item.query('./Salary').value('.', 'INT') Salary
 FROM @xml.nodes('/ROWS/ROW') AS T(Item)
RETURN 0
END

SP Implementation: parsing element based XML: Usage

Here I am creating an XML variable and populating it with valid XML having elements, which I am referring into the SP. If you do not have any element value, then you can omit the whole element, e.g.

<ROWS>
<ROW> 
<Name>Tom</Name> 
<Salary>2100</Salary> 
</ROW>
<ROW> 
<Name>Nuk</Name> 
</ROW>
<ROW> 
<Name>Mat</Name> 
<Salary>2600</Salary> 
</ROW>
</ROWS>

In the above XML I do not have any data pertaining to second employee "Nuk". So did not include the salary element for it, at all.

Declare @x XML
SET @x = N'<ROWS>
<ROW> 
<Name>Tom</Name> 
<Salary>2100</Salary> 
</ROW>
<ROW> 
<Name>Nuk</Name> 
<Salary>2200</Salary> 
</ROW>
<ROW> 
<Name>Gilbert</Name> 
<Salary>2300</Salary> 
</ROW>
<ROW> 
<Name>Mat</Name> 
<Salary>2600</Salary> 
</ROW>
</ROWS>'
EXEC InsertEmp3 @x
SELECT * FROM Emp

Comparison between parsing of delimited string and xml data

In this two part article, I have tried to present the following two efficient approaches to work around the problem of sending multiple rows to the data base for update:

1. Delimited String Approach - explained in part I of this article
2. XML data approach - explained in part II of this article.

I now present a comparison of both these approaches highlighting the pros and cons of each approach.

Delimited string Approach

Pros

• Easy to parse at the database side.
• Easy to create at the application layer.

Cons

• Manual parsing, need to write logic to parse. (In our case we have written table valued functions.)
• Need to pass as many delimited strings as the number of parameters.
• Since, delimited string would be passed as a string data type, so limitation of size of string data supported in SQL Server 2005 comes into picture (4000 / 8000 characters max including the delimiter character).
• Choosing a delimiter character, as it should never occur in your data.

XML Data

Pros

• Supports large size of data (up to 2 GB).
• Only one parameter needs to be sent from application layer.
• No manual parsing, handled by XQuery functions.
• Passed XML can be validated with an XSD.

Cons

• Complex to parse (need to have knowledge of XQuery functions).
• Difficult to create XML data at the application layer.

Conclusion

In this article, we discussed about the XQuery functions and how to convert an XML into a table using them.
In the first article of the series, "Sending Multiple rows to Database for Update - Part I", we already discussed about, how we can send a delimited string to the database for update.

Hope these two articles help you to modify multiple records in database in a single database call.

These articles would also be helpful where an application works in disconnected environment. A user can save data in a disconnected environment using either of these two approaches. When the user will be online or connected to database and wants to update the modified records, all that data can be passed to the database for updating using either a delimited string or an XML.