How to Read XML file

Objective: To read xml in sql server.
Assume file is stored in D:\DailyScripts\xml\customer.xml
File Name: Customer.xml
Reference: http://msdn.microsoft.com/en-us/library/ms186918.aspx
Eg: http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/
File Start:

File End

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileName = 'D:\DailyScripts\xml\customer.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
SET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK = @x
END
SELECT @FileContents as FileContents
DROP TABLE #tempXML
DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc =@FileContents
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')

SQL Server Service Pack Version Finder

Objective: To find out the SQL server Service Pack Version
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
go
select @@version

Way to find Blocking Information - SQL Server

Objective: Below script is to find the blocking information in sql server
Declare @wTable Table (spid int, blocked int, loginame varchar(100), dbName varchar(100), last_batch dateTime)
Insert into @wTable
select spid, blocked, loginame, db_name(dbid), last_batch from sys.sysprocesses (nolock)--where blocked <> 0
select spid,blocked,loginame,dbName,last_batch from @wtable where blocked <> 0
Union all
select spid,blocked,loginame,dbName,last_batch from @wtable where spId in(select blocked from @wtable where blocked <> 0)
order by blocked

Server Roles - SQL Server 2005

Script for Server Roles in SQL Server 2005:
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'bulkadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'dbcreator'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'diskadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'processadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'securityadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'serveradmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'setupadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'sysadmin'
GO