Working with XML Data in SQL Server 2005

XML is one of the data types in SQL Server 2005.It is considered as one of the significant changes introduced in SQL Server 2005.In the previous versions, storing and retrieving XML data were possible but it was not as easy and extensible as it is in the current version. The XML data type lets the user to store XML data in the SQL Server database. In this article we will be discussing how to store, retrieve and query XML data.

Support for XML is integrated into all the components of SQL Server 2005.The SQL Server 2005 supports XML in the following way:

SQL Server 2005 supports XML data type.
The ability to specify an XQuery query against XML data stored in columns and variables of the xml type.
Enhancements to OPENROWSET to allow bulk loading of XML data.
Enhancements to the FOR XML clause and OPENXML function introduced in SQL Server 2000.
Let’s now take a look at an example. This example demonstrates how to create a table that contains an XML column. We will also insert some records into the table.

1. Create table with XML column Create table dbo.Student(StudentID int,StudentName varchar(50),Studentcontactdetails XML)

2. Insert valid XML data into the table:Insert into dbo.Student values(1,’ABC’,’123,XYZ street,London’)

If we try to insert invalid XML, it will result in an error message.Insert into dbo.Student values(1,’ABC’,’123,XYZ street,London’)Msg 9400, Level 16, State 1, Line 1XML parsing: line 1, character 36, unexpected end of input

We can also create variables of XML type.Declare @x xml

Typed vs. Untyped XML

Untyped XML can be stored in any form. The XML should be a well formed one. When the user inserts a value to the XML column, a check will occur to see whether the data that is about to be inserted matches the XML standard. The value is not validated against any XML schema.Untyped xml provides a more flexible way to store data.
Typed XML is used when the user wants to validate the XML with an XML schema. The XML schema has to be mentioned when creating the XML datatype.This is done by referring to the XML schema.XML schema has to be initially stored and catalogued in the database. Thus the XML that is validated against a XML schema is Typed XML.
The typed XML is declared in the following way: Declare @x XML(schema.xmlschemacollection)
The typed XML is suitable in scenarios where the XML data which is stored in the database has to match a strict definition such as, for example, an invoice. The XML schema can be created as follows:CREATE XML SCHEMA COLLECTION [ . ]sql_identifier AS Expression

After the XML schema is catalogued, the information about it can be obtained by using the “XML_schema_namespace” function.
Example:SELECT xml_schema_namespace(N’dbo’,N’Chemicals’)

Once the XML schema is defined, we can use it and refer to it in CREATE TABLE or DECLARE XML statement.


1)Declare @xml xml(Chemicals)set @xml=’Sulphuric AcidH2SO4′
2) Insert into students (studentname,Labid,Experimentid,Experimentchemical)values (‘ABC’,1,1,’Sulphuric AcidH2SO4′)

Querying XML Data

The methods which can be used to retrieve data from an xml datatype are as follows:

1) Query method: This method returns a fragment of untyped XML.

The following example shows how to return a value from within an XML column.

Example:select experimentchemical.query(‘/root/ChemicalName’) from students

Result:This will result in a partial result set but will return a fragment of untyped XML.Sulphuric Acid

To retrieve only the values,we can use the data function.

Select experiment.query(‘data(/root/ChemicalName)’) from students

2) Value Method:The value method is similar to the query method.The only difference is that the value method will accept an additional parameter to determine the resulting scalar datatype.


If the user want to see the second customer’s customer ID then the query would be as follows:select @x.value(‘(/root/CustomerDescription/@CustID)[2]’,’int’)
3) Exist method: The exist method takes an expression as input.This expression selects a single node within the XML document and returns true(1) if that node exists or false(0) if it does not.

Example:select Experimentchemical.exist(‘/root/ChemicalName’) from studentsThe above query will return true for all items where the student has the chemical details to be used in the experiment.

Select experiment.exist(‘/root/ChemicalName’) from students

The exist method can be used in the where clause in the following way:select * from students where Experimentchemical.exist(‘/root/ChemicalName’)=1

4)Modify Method: Modify method can be used to manipulate the XML data stored in a table.The modify method consists of three substatements:


1)declare @x xmldeclare @custid intset @x=’1234567890143454667812345678901434546678’select @xset @x.modify(‘delete /root/CustomerDescription/@CustID’)select @xResult:1234567890143454667812345678901434546678

2)To insert a new node or fragment of XML,we can use the INSERT statement.The syntax for it is as follows:Syntax:insert Expression1 ({as first as last} into after beforeExpression2)
Arguments:Expression1 Identifies one or more nodes to insert. into Nodes identified by Expression1 are inserted as direct descendents (child nodes) of the node identified by Expression2. If the node in Expression2 already has one or more child nodes, the user must use either as first or as last to specify where the new node has to be added. For example, at the start or at the end of the child list, respectively. after Nodes identified by Expression1 are inserted as siblings directly after the node identified by Expression2. before Nodes identified by Expression1 are inserted as siblings directly before the node identified by Expression2. Expression2 Identifies a node. The nodes identified in Expression1 are inserted relative to the node identified by Expression2.


USE AdventureWorks;GODECLARE @myDoc xml SET @myDoc = ‘ ‘

— insert first feature child (no need to specify as first or as last)
SET @myDoc.modify(‘
insert 3 year parts and labor extended maintenance is available
into (/Root/ProductDescription/Features)[1]’)

3 year parts and labor extended maintenance is available

5) Nodes Method: The nodes method can be used to extract data from an XML document and use that to generate subnodes that can be used for various purposes,such as,to create new content or insert content into new tables.

declare @x xml
set @x=’MUMMumbaiMASMadras’
Select T.c.query(‘.’) as resultfrom @x.nodes(‘/Root/row’) T(c)

Eventdata function

Eventdata function is returns information about server or database events. EVENTDATA is called when an event notification fires, and the results are returned to the specified service broker. EVENTDATA can also be used inside the body of a DDL or logon trigger.Mor information about DDL triggers can be found in the article “Understanding DDL triggers in SQL Server 2005” .Eventdata returns a value of type XML. The XML schema includes information about the following:

The time of the event.
The System Process ID (SPID) of the connection when the trigger executed.
The type of event that fired the trigger
Depending on the event type, the schema then includes additional information such as the database in which the event occurred, the object against which the event occurred, and the Transact-SQL statement of the event.


The following example creates a DDL trigger to prevent new tables from being created in the database. The Transact-SQL statement that fires the trigger is captured by using XQuery against the XML data that is generated by Eventdata.

USE AdventureWorks;





SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)

RAISERROR (‘New tables cannot be created in this database.’, 16, 1)



–Test the trigger.

CREATE TABLE NewTable (Column1 int);


–Drop the trigger.




XML Indexes:

For the faster retrieval of XML data, the user can create indexes on these columns.XML indexes fall into the following categories:

1) Primary XML index dsjkasdj

2) Secondary XML index

The first index on the xml type column must be the primary XML index. Using the primary XML index, the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY. Depending on the type of queries, these secondary indexes might help improve query performance.

Primary XML Index:The primary XML index is a shredded and persisted representation of the XML BLOBs in the xml data type column. For each XML binary large object (BLOB) in the column, the index creates several rows of data. The number of rows in the index is approximately equal to the number of nodes in the XML binary large object.

Each row stores the following node information:

Tag name such as an element or attribute name.
Node value.
Node type such as an element node, attribute node, or text node.
Document order information, represented by an internal node identifier.
Path from each node to the root of the XML tree. This column is searched for path expressions in the query.
Primary key of the base table.
Secondary XML Index:To enhance search performance, secondary XML indexes can be created. A primary XML index must first exist before the user creates secondary indexes. These are the types of secondary XML index:

Path secondary XML index: – If the queries based on path instructions, Path secondary index may be able to speed up the search. The Path secondary index is helpful when you have queries that specify exist() method in the WHERE clause.
Value secondary XML index: – If queries are value based and the path is not fully specified or it includes a wildcard, we can obtain faster results by building a Value secondary XML index that is built on node values in the primary XML index.
PROPERTY secondary XML index:- Queries that retrieve one or more values from individual XML instances might benefit from a PROPERTY index. This scenario occurs when you retrieve object properties by using the value() method of the xml type and when the primary key value of the object is known.

Conclusion:Thus we had a look at how the power of XML can be fully captured in SQL Server 2005.Happy XMLing!!!

Posted in Microsoft Technology Tagged with: