Modify your XML document using SQL

Many of the situations, we will have to modify the xml that is passed on to the sql server.

Some of the common operations you might need to handle in SQL are

• adding an element
• adding an attribute
• updating an attribute
• delete an element
• delete an attribute

 Let us try this with a sample document.

Declare @myXML xml

SELECT @myXML = '<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
</CUSTOMERSDATA>'


Add an Element

SET @myXML.modify('insert <CUSTOMER FName="Ravi" LName="Krishna" City="Chicago" State="IL" />
into (/CUSTOMERSDATA)[1]') ;


select @myXML

Output:

<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
<CUSTOMER FName="Ravi" LName="Krishna" City="Chicago" State="IL" />
</CUSTOMERSDATA>

Add an attribute

SET @myXML.modify('insert attribute Phone {"333-333-3333" }
into (/CUSTOMERSDATA/CUSTOMER[@FName="Ravi"])[1]') ;


select @myXML


Output:
<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
<CUSTOMER FName="Ravi" Phone="333-333-3333" LName="Krishna" City="Chicago" State="IL" />
</CUSTOMERSDATA>



Updating an attribue

SET @myXML.modify('replace value of (/CUSTOMERSDATA/CUSTOMER[@FName="Ravi"]/@Phone)[1] with "444-444-4444"') ;


select @myXML

Output:<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
<CUSTOMER FName="Ravi" Phone="444-444-4444" LName="Krishna" City="Chicago" State="IL" />
</CUSTOMERSDATA>



Delete attribue

SET @myXML.modify('delete (/CUSTOMERSDATA/CUSTOMER[@FName="Ravi"]/@Phone)[1]') ;


select @myXML

Output:
<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
<CUSTOMER FName="Ravi" LName="Krishna" City="Chicago" State="IL" />
</CUSTOMERSDATA>



Delete Element

SET @myXML.modify('delete (/CUSTOMERSDATA/CUSTOMER[@FName="Ravi"])[1]') ;


select @myXML


Output:

<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
</CUSTOMERSDATA>

Using Xpaths in Sql server

When we were using Sql 2000, we had very less functionalities available for xml operations. After Sql 2005, xml operations are very easy and make it very simple to handle. We have new datatype XML which allows to do lot on XML on sql server side. I am just going to take a simple example of a table which will save the xml data and how to fetch using conditions.


Ok. Here is our table. Let us call it as Contents

CREATE TABLE [dbo].[Contents](
[ContentID] [int] NOT NULL,
[ContentData] [xml] NULL,
[ContentCreatedDate] [datetime] NULL
)


We have column ContentData which is of type xml. I am going to insert few records to this table now.

insert into Contents values (1,'<customer><info id="1" fname="Subbu" lname="P"> <address address1="#444 bangalore" phone="12345"></customer>',GETDATE())

insert into Contents values (2,'<customer><info id="2" fname="Ram" lname="T"> <address address1="#111 Mumbai" phone="12345"></customer>',GETDATE())

insert into Contents values (3,'<customer><info id="3" fname="Raj" lname="M"> <address address1="#222 Pune" phone="12345"></customer>',GETDATE())

insert into Contents values (4,'<customer><info id="4" fname="Ravi" lname="R"> <address address1="#333 Mysore" phone="12345"></customer>',GETDATE())

Now let’s do a select from Contents table. You will get below result set.

You can see all the data inserted and the xml. Now lets say you need to pick the data based on the xml value. Let’s consider you need to find all the Customers from Pune. How to retrieve the data?


Select *,ContentData.value('(/Customer/Address/@Address1)[1]','varchar(20)') as Address from Contents where ContentData.value('(/Customer/Address/@Address1)[1]','varchar(20)') like '%Pune'

So here is our result set

We have passed the Xpath /Customer/Address/@Address1 to the value method which will process the xml and provide the result set. The value method takes two parameters.

With XML, we can also pass information between sps without worrying about the paramenters which gives lots of flexibility on design. I hope this small piece gives a brief idea on how to use xml and xpaths on your sql.

Loading data as XML from DB

In most of the applications, the data will be loaded as Dataset or datareader from Sql server from the back end DB. If we are going to look at service oriented approach, the services normally return xml. Most of the time we get data from SQl and convert to xml and send it. We could get the data directly from Db itself.

From the stored procedure here is my select statement

This statement selects the data from Department table which has columns DeptID,DeptName,IsActive

SELECT
1 AS TAG, NULL AS PARENT,
DeptID AS [Department!1!DeptID],
ISNULL(DeptName, '') AS [Department!1!DeptName],
FROM Department (NOLOCK)
WHERE IsActive = 'Y'
ORDER BY DeptName
FOR XML EXPLICIT, ROOT('Department')

The first two columns Tag and PARENT are meta coulmns. for more details on these tags you can check http://msdn.microsoft.com/en-us/library/ms189068.aspx

The xml returned would be

<Department> < DeptID="1" DeptName="Administration"/> < /Department >

Loading the Xml Data using C#

SqlConnection dbConn = new SqlConnection(connectionstring);
dbCommand.Connection = dbConn;
dbConn.Open();
XmlReader dbReader;
dbReader = dbCommand.ExecuteXmlReader();
dbReader.Read();
StringBuilder str = new StringBuilder();
do
{
str.Append(dbReader.ReadOuterXml());

}
while (dbReader.ReadState != ReadState.EndOfFile);
dbConn.Close();
return str.ToString();

Above statement will return the xml data returned from DB as a string format which can be returned from service directly.

But it is always a good practice to bind the data returned from DB to a schema for the datatype check and perorm the validation. The above method returns xml but does not have any infromation on the schema of the data. So this may not be suitable in all the situations.

ARITHABORT and Xml Datatype on SQL

While using XML datatype on Sql 2005 or 2008, I got into a strange problem. The sql queries used work perfectly when I run it on query analyzer but it used to fail when I put that on a stored procedure. The sql was using xml datatype and it was performing some xml operations.

After researching, I found that if we use xml datatype, we need to set ARITHABORT ON.
This is the requirement as stated here

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354563

"Currently, there is a reqiurement that ARITHABORT must be ON when you use XML methods and XQuery. However, as long as the compatibility level is >= 90,ANSI_WARNINGS on implies ARITHABORT ON, so this check is not needed. Many client APIs connect by default with this setting off, but ANSI_WARNINGS on,so users need to take extra precautions to deal with this and it can causeconfusion and misery. For instance, I recently encountered a case where someonerun into his Agent job failing because of this"


So make a point to set ARITHABORT ON whenever you use xml datatype on your stored procedures.