Difference between revisions of "HumanResources.vJobCandidateEmployment (view)"
| Line 85: | Line 85: | ||
{| 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" | ||
| + | | '''Dependency Type | ||
| + | | '''Object Type | ||
| '''Referenced Object | | '''Referenced Object | ||
| − | |||
| − | |||
|- | |- | ||
| + | | Select | ||
| + | | Table | ||
| [[HumanResources.JobCandidate_(table)|HumanResources.JobCandidate]] | | [[HumanResources.JobCandidate_(table)|HumanResources.JobCandidate]] | ||
| − | + | ||
| − | |||
|- | |- | ||
| − | |||
| Schema | | Schema | ||
| Schema | | Schema | ||
| + | | [[HumanResources_(schema)|HumanResources]] | ||
| + | |||
|} | |} | ||
Revision as of 06:39, 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);
| Dependency Type | Object Type | Referenced Object |
| Select | Table | HumanResources.JobCandidate |
| Schema | Schema | HumanResources |
