Difference between revisions of "HumanResources.vJobCandidateEmployment (view)"
| Line 43: | Line 43: | ||
| '''view | | '''view | ||
| HumanResources.vJobCandidateEmployment | | HumanResources.vJobCandidateEmployment | ||
| − | |- | + | |- valign="top" |
| '''Description | | '''Description | ||
| Line 88: | Line 88: | ||
| '''Object Type | | '''Object Type | ||
| '''Dependency Type | | '''Dependency Type | ||
| − | + | ||
|- | |- | ||
| [[HumanResources.JobCandidate_(table)|HumanResources.JobCandidate]] | | [[HumanResources.JobCandidate_(table)|HumanResources.JobCandidate]] | ||
| Table | | Table | ||
| Select | | Select | ||
| + | |- | ||
| + | | [[.HumanResources_(schema)|.HumanResources]] | ||
| + | | Schema | ||
| + | | Schema | ||
|} | |} | ||
Revision as of 06:25, 30 November 2009
wikibot
| view | HumanResources.vJobCandidateEmployment |
CREATE VIEW [HumanResources].[vJobCandidateEmployment]
AS
SELECT
jc.[JobCandidateID]
,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.StartDate]
,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.EndDate]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.OrgName)[1]', 'nvarchar(100)') AS [Emp.OrgName]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.JobTitle)[1]', 'nvarchar(100)') AS [Emp.JobTitle]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Responsibility)[1]', 'nvarchar(max)') AS [Emp.Responsibility]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.FunctionCategory)[1]', 'nvarchar(max)') AS [Emp.FunctionCategory]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.IndustryCategory)[1]', 'nvarchar(max)') AS [Emp.IndustryCategory]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(max)') AS [Emp.Loc.CountryRegion]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.State)[1]', 'nvarchar(max)') AS [Emp.Loc.State]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.City)[1]', 'nvarchar(max)') AS [Emp.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/Employment') AS Employment(ref);
automatically generated
| view | HumanResources.vJobCandidateEmployment |
| Description | Displays the content from each employement history 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].[vJobCandidateEmployment]
AS
SELECT
jc.[JobCandidateID]
,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.StartDate]
,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.EndDate]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.OrgName)[1]', 'nvarchar(100)') AS [Emp.OrgName]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.JobTitle)[1]', 'nvarchar(100)') AS [Emp.JobTitle]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Responsibility)[1]', 'nvarchar(max)') AS [Emp.Responsibility]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.FunctionCategory)[1]', 'nvarchar(max)') AS [Emp.FunctionCategory]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.IndustryCategory)[1]', 'nvarchar(max)') AS [Emp.IndustryCategory]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(max)') AS [Emp.Loc.CountryRegion]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.State)[1]', 'nvarchar(max)') AS [Emp.Loc.State]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.City)[1]', 'nvarchar(max)') AS [Emp.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/Employment') AS Employment(ref);
| Referenced Object | Object Type | Dependency Type |
| HumanResources.JobCandidate | Table | Select |
| .HumanResources | Schema | Schema |
