HumanResources.vJobCandidateEducation View

Description

Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.

Properties

Creation Date	08/01/2010 08:41
Is Schema Bound
Encrypted
Ansi Nulls

Columns

	Column Name	Description	Datatype	Length	Allow Nulls	Default	Formula
	JobCandidateID		int	4

Objects that HumanResources.vJobCandidateEducation depends on

Database Object	Object Type	Description	Dep Level
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	3
HumanResources.Employee	Table	Employee information such as salary, department, and title.	2
dbo.Flag	User Defined Data Type		3
HumanResources.JobCandidate	Table	Résumés submitted to Human Resources by job applicants.	1
dbo.Name	User Defined Data Type		4
dbo.NameStyle	User Defined Data Type		4
dbo.Phone	User Defined Data Type		4

View Definition

CREATE VIEW [HumanResources].[vJobCandidateEducation] 
AS 

SELECT 
jc.[JobCandidateID] 
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.Level)[1]', 'nvarchar(max)') AS [Edu.Level]
,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.StartDate] 
,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.EndDate] 
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.Degree)[1]', 'nvarchar(50)') AS [Edu.Degree]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.Major)[1]', 'nvarchar(50)') AS [Edu.Major]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.Minor)[1]', 'nvarchar(50)') AS [Edu.Minor]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.GPA)[1]', 'nvarchar(5)') AS [Edu.GPA]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.GPAScale)[1]', 'nvarchar(5)') AS [Edu.GPAScale]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.School)[1]', 'nvarchar(100)') AS [Edu.School]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Edu.Loc.CountryRegion]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Edu.Loc.State]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
(Edu.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Edu.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
/Resume/Education') AS [Education](ref);

Related Objects