Example Needed

I am new to XML in SQL Server 2005. I was wondering if someone would be so
kind as to show me how I might do the following? In the AdventureWorks
database there is a table named HumanResources.JobCandidate. There is an XML
field in that table named 'Resume'. How would I retrieve say the last name
and job title of each record in that table? What would the SQL statement
look like to do that?
Thanks very much. Amos.
Hello Amos,
select Resume.value(
'declare namespace r="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(r:Resume/r:Name/r:Name.Last)[1]', 'nvarchar(100)') as [Last Name],
Resume.value('declare namespace r="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(r:Resume/r:Employment/r:Emp.JobTitle)[1]', 'nvarchar(100)') as [Job Title]
FROM HumanResources.JobCandidate
Dan

> I am new to XML in SQL Server 2005. I was wondering if someone would
> be so kind as to show me how I might do the following? In the
> AdventureWorks database there is a table named
> HumanResources.JobCandidate. There is an XML field in that table named
> 'Resume'. How would I retrieve say the last name and job title of each
> record in that table? What would the SQL statement look like to do
> that?
> Thanks very much. Amos.
>
|||Thank you Dan. Very kind of you.
Amos.
"Dan" <dsullivanATdanal.com> wrote in message
news:d25ffa451d4758c7df7ede9676b6@.news.microsoft.c om...
> Hello Amos,
> select Resume.value(
> 'declare namespace
> r="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
> (r:Resume/r:Name/r:Name.Last)[1]', 'nvarchar(100)') as [Last Name],
> Resume.value('declare namespace
> r="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
> (r:Resume/r:Employment/r:Emp.JobTitle)[1]', 'nvarchar(100)') as [Job
> Title]
> FROM HumanResources.JobCandidate
> Dan
>
>