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','<ROOT><Student>123,XYZ street,London</Student></ROOT>')

If we try to insert invalid XML, it will result in an error message.
Insert into dbo.Student values(1,'ABC','<ROOT><Student>123,XYZ street,London')
Msg 9400, Level 16, State 1, Line 1
XML 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 [ <relational_schema>. ]sql_identifier AS Expression

• relational_schema:-Identifies the relational schema name. If not specified, default relational schema is assumed.
• sql_identifier :-Is the SQL identifier for the XML schema collection
• Expression:-Is a string constant or scalar variable. It can be varchar, varbinary,       nvarchar, nvarbinary, or xml type.

Example:

CREATE XML SCHEMA COLLECTION Chemicals AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="root">
<complexType>
<sequence>
<element name="ChemicalName" type="string"/>
<element name="Symbol" type="string"/>
</sequence>
</complexType>
</element>
</schema>'
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.

Example:
1)
Declare @xml xml(Chemicals)
set @xml='<root><ChemicalName>Sulphuric Acid</ChemicalName><Symbol>H2SO4</Symbol></root>'
2)
Insert into students (studentname,Labid,Experimentid,Experimentchemical)
values ('ABC',1,1,'<root><ChemicalName>Sulphuric Acid</ChemicalName><Symbol>H2SO4</Symbol></root>')

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.
<ChemicalName>Sulphuric Acid</ChemicalName>

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


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.
Example:


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 students
The above query will return true for all items where the student has the chemical details to be used in the experiment.



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:

• INSERT
• DELETE
• REPLACE

Example:
1)
declare @x xml
declare @custid int
set @x='<root>
<CustomerDescription CustID="101" CustomerName="ABCL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
<CustomerDescription CustID="102" CustomerName="HAL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
</root>'
select @x
set @x.modify('delete /root/CustomerDescription/@CustID')
select @x
Result:
<root>
<CustomerDescription CustomerName="ABCL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
<CustomerDescription CustomerName="HAL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
</root>

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 | before
Expression2
)
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.

Example:
USE AdventureWorks;
GO
DECLARE @myDoc xml      
SET @myDoc = '<Root>      
<ProductDescription ProductID="1" ProductName="Road Bike">      
<Features>      
</Features>      
</ProductDescription>      
</Root>'      
SELECT @myDoc      
-- insert first feature child (no need to specify as first or as last)      
SET @myDoc.modify('      
insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
into (/Root/ProductDescription/Features)[1]')
SELECT @myDoc 
Result:
<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>
For more examples,refer to” insert(XML DML)” in SQL Server 2005 books online.

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.
Example:
declare @x xml
set @x='<Root><row id="1"><AirportCode>MUM</AirportCode><AirportName>Mumbai</AirportName></row>
<row id="2"><AirportCode>MAS</AirportCode><AirportName>Madras</AirportName></row>
<row id="3"></row></Root>'
Select T.c.query('.') as result
from @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.
Example:

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;
GO
CREATE TRIGGER safety
ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'CREATE TABLE Issued.'
SELECT EVENTDATA().value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
RAISERROR ('New tables cannot be created in this database.', 16, 1)
ROLLBACK
;
GO
--Test the trigger.
CREATE TABLE NewTable (Column1 int);
GO
--Drop the trigger.
DROP TRIGGER safety
ON DATABASE
GO
Result:



Messages:

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
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:

1. Tag name such as an element or attribute name.
2. Node value.
3. Node type such as an element node, attribute node, or text node.
4. Document order information, represented by an internal node identifier.
5. Path from each node to the root of the XML tree. This column is searched for path expressions in the query.
6. 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.