Web Listing 1: Using the XML Data Type
USE AdventureWorks;

-- Declare a variable of Type XML
DECLARE @XML xml;

-- Issue a relational query, using FOR XML AUTO, and store the results in
-- the XML variable
SET @XML = (SELECT	C.FirstName, C.LastName, C.EmailAddress,
					E.Title, E.HireDate, E.VacationHours
			FROM	Person.Contact C INNER JOIN
					HumanResources.Employee E ON
						C.ContactID = E.ContactID
			WHERE	C.ContactID = 1001
			FOR		XML AUTO
		   );

-- Examine the results
SELECT @XML As TheXML;

-- Use the .value method to return a specific value within the XML variable
/*
  Value (XQuery, SQLType) - Performs an XQuery against the XML and returns
							a value of SQL type.
*/
DECLARE @VacationHours smallint;
SET		@VacationHours =  @XML.value('(/C/E/@VacationHours)[1]', 'smallint' )
SELECT	@VacationHours AS VacationHours;

-- The XML data type can also be used in table definitions.  Consider the
--	HumanResource.JobCandidate table as an example
SELECT	@XML = Resume
FROM	HumanResources.JobCandidate
WHERE	JobCandidateID = 1;

SELECT	@XML AS TheXML;