Playing between SQL Server2005 and XML
for IT Guys

Hear I'm going to talk about SQL server 2005 concepts with xml.....

just have a look on that....

Use Adventureworks database in SQL server2005

Demo for the FOR XML ROW
-----------------------------------------
select
Department.[DepartmentID],
History.[EmployeeID],
History.[StartDate],
Department.[Name] as DepartmentName,
DateDiff(year,History.[StartDate],getdate()) as YearsToDate

from HumanResources.Department,
HumanResources.EmployeeDepartmentHistory History

where
Department.DepartmentID=History.DepartmentID

and

History.Enddate is null

order by
Department.[DepartmentId],
History.[StartDate]

for XML RAW('OLDESTEMPLOYEEBYDEPARTMENT'),ELEMENTS

Demo for the FOR XML AUTO
-------------------------------------------
select 
Department.[DepartmentID],
History.[EmployeeID],
History.[StartDate],
Department.[Name] as DepartmentName,
DateDiff(year,History.[StartDate],getdate()) as YearsToDate

from HumanResources.Department,
HumanResources.EmployeeDepartmentHistory History
where
Department.DepartmentID=History.DepartmentID
and
History.Enddate is null
order by Department.[DepartmentId],History.[StartDate]
for XML AUTO,ELEMENTS

Demo for the FOR XML PATH
-------------------------------------------
select

History

.[StartDate]'@StartDate',

Department

.[DepartmentID]'Department/@id',

Department

.[Name] 'comment()', --Important it is case sensitive if you will type Comments then Error

History

.[EmployeeID]'Department/Employee/@id',

'Years in role:'

'Department/Employee/data()',

DateDiff

(year,History.[StartDate],getdate())'Department/Employee/data()'

from HumanResources.Department,

HumanResources

.EmployeeDepartmentHistory History

where

Department

.DepartmentID=History.DepartmentID

and

History

.Enddate is null

order

by Department.[DepartmentId],History.[StartDate]

for

XML PATH('ForEachRow')