articles >> developer >> Working with XML Data in SQL Server ...

Working with XML Data in SQL Server 2005

By : S.Srivathsani Aug 06, 2007

Page 3 / 3



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.

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