|
Home > Archive > MS SQL XML > January 2006 > Example Needed
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Amos Soma 2006-01-04, 8:24 pm |
| 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.
>
| |
| Amos Soma 2006-01-05, 9:23 am |
| Thank you Dan. Very kind of you.
Amos.
"Dan" <dsullivanATdanal.com> wrote in message
news:d25ffa451d4758c
7df7ede9676b6@news.microsoft.com...
> 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
>
>
>
>
|
|
|
|
|