Difference between revisions of "HumanResources.vJobCandidateEducation (view)"
| Line 3: | Line 3: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- | |- | ||
| − | | ''' | + | | '''View |
| HumanResources.vJobCandidateEducation | | HumanResources.vJobCandidateEducation | ||
|- valign="top" | |- valign="top" | ||
| Line 11: | Line 11: | ||
|} | |} | ||
| + | |||
| + | === Source === | ||
<pre> | <pre> | ||
CREATE VIEW [HumanResources].[vJobCandidateEducation] | CREATE VIEW [HumanResources].[vJobCandidateEducation] | ||
| Line 45: | Line 47: | ||
</pre> | </pre> | ||
| − | + | === References === | |
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" | ||
| Line 56: | Line 58: | ||
| Table | | Table | ||
| [[HumanResources.JobCandidate_(table)|HumanResources.JobCandidate]] | | [[HumanResources.JobCandidate_(table)|HumanResources.JobCandidate]] | ||
| + | |||
| + | |- | ||
| + | | Schema | ||
| + | | Schema | ||
| + | | [[HumanResources_(schema)|HumanResources]] | ||
|} | |} | ||
| − | |||
Revision as of 00:09, 3 February 2010
wikibot
| View | HumanResources.vJobCandidateEducation |
| 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. |
Source
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);
References
| Dependency Type | Object Type | Referenced Object |
| Select | Table | HumanResources.JobCandidate |
| Schema | Schema | HumanResources |
automatically generated
| view | HumanResources.vJobCandidateEducation |
| 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. |
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);
| Dependency Type | Object Type | Referenced Object |
| Select | Table | HumanResources.JobCandidate |
| Schema | Schema | HumanResources |
