Execute the following script in Query Editor to demonstrate the use of XML value method for information retrieval.

USE AdventureWorks;

DECLARE @Employees XML

SET @Employees = (

SELECT

      [Name]=FirstName+' '+LastName,

      e.Title,

      EmployeeID

FROM HumanResources.Employee e

JOIN Person.Contact c

ON e.ContactID = c.ContactID

FOR XML RAW('Employee'), ROOT('Employees'), TYPE)

SELECT

EmployeeName      = e.Employee.value('@Name[1]', 'VARCHAR(40)'),

Title             = e.Employee.value('@Title[1]', 'VARCHAR(30)'),

EmployeeID        = e.Employee.value('@EmployeeID[1]', 'INT')

FROM @Employees.nodes('//Employees/Employee') AS e(Employee)

ORDER BY EmployeeName

GO