View: vJobCandidateEmployment

Properties

Property	Value
Name	vJobCandidateEmployment
ID	1899153811
Schema	HumanResources
Owner	dbo
Encrypted
Schema Bound
With Check Option
ANSI NULLS
Uses Quoted Identifier
Has Opaque Metadata
Published
Schema Published
Replicated
Has Replication Filter
Has Unchecked Assembly Data
Date Correlation View
Uses Database Collation
Tracked by CDC
MS Shipped
Modify Date	8/8/2010 5:23:45 PM
Create Date	8/8/2010 5:23:45 PM

Extended Properties

Name	Value
MS_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.

View Columns

Column Name	Datatype	Nullable	Is Identity
JobCandidateID	int
Emp.StartDate	datetime
Emp.EndDate	datetime
Emp.OrgName	nvarchar(100)
Emp.JobTitle	nvarchar(100)
Emp.Responsibility	nvarchar(max)
Emp.FunctionCategory	nvarchar(max)
Emp.IndustryCategory	nvarchar(max)
Emp.Loc.CountryRegion	nvarchar(max)
Emp.Loc.State	nvarchar(max)
Emp.Loc.City	nvarchar(max)

Indexes

No view indexes defined.

Object Level Permissions

No object level permissions defined.

Column Level Permissions

No explicit column permissions granted to this object

Objects that depend on [vJobCandidateEmployment]

HumanResources.vJobCandidateEmployment

Objects on which [vJobCandidateEmployment] depends

HumanResources.vJobCandidateEmployment
- HumanResources.JobCandidate
  - HumanResources.Employee
    - dbo.Flag
    - Person.Person
      - dbo.Name
      - dbo.NameStyle
      - Demographics.exist
      - Person.AdditionalContactInfoSchemaCollection
      - Person.BusinessEntity
      - Person.IndividualSurveySchemaCollection
  - HumanResources.HRResumeSchemaCollection

SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON 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); EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'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.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'VIEW',@level1name=N'vJobCandidateEmployment'

Script

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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);
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'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.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'VIEW',@level1name=N'vJobCandidateEmployment'