HumanResources.vJobCandidate

CREATE VIEW [HumanResources].[vJobCandidate]
AS
SELECT
jc.[JobCandidateID]
,jc.[EmployeeID]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.Prefix)[1]', 'nvarchar(30)') AS [Name.Prefix]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.First)[1]', 'nvarchar(30)') AS [Name.First]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.Middle)[1]', 'nvarchar(30)') AS [Name.Middle]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.Last)[1]', 'nvarchar(30)') AS [Name.Last]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.Suffix)[1]', 'nvarchar(30)') AS [Name.Suffix]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Skills)[1]', 'nvarchar(max)') AS [Skills]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.Type)[1]', 'nvarchar(30)') AS [Addr.Type]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Addr.Loc.CountryRegion]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Addr.Loc.State]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Addr.Loc.City]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.PostalCode)[1]', 'nvarchar(20)') AS [Addr.PostalCode]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/EMail)[1]', 'nvarchar(max)') AS [EMail]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/WebSite)[1]', 'nvarchar(max)') AS [WebSite]
,jc.[ModifiedDate]
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') AS Resume(ref);

-- Views
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.', N'SCHEMA', [Person], N'VIEW', [vAdditionalContactInfo], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Employee names and addresses.', N'SCHEMA', [HumanResources], N'VIEW', [vEmployee], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Returns employee name, title, and current department.', N'SCHEMA', [HumanResources], N'VIEW', [vEmployeeDepartment], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Returns employee name and current and previous departments.', N'SCHEMA', [HumanResources], N'VIEW', [vEmployeeDepartmentHistory], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Individual customers (names and addresses) that purchase Adventure Works Cycles products online.', N'SCHEMA', [Sales], N'VIEW', [vIndividual], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.', N'SCHEMA', [Sales], N'VIEW', [vIndividualDemographics], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Job candidate names and resumes.', N'SCHEMA', [HumanResources], N'VIEW', [vJobCandidate], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', 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.', N'SCHEMA', [HumanResources], N'VIEW', [vJobCandidateEmployment], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'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.', N'SCHEMA', [HumanResources], N'VIEW', [vJobCandidateEducation], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Product names and descriptions. Product descriptions are provided in multiple languages.', N'SCHEMA', [Production], N'VIEW', [vProductAndDescription], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data.', N'SCHEMA', [Production], N'VIEW', [vProductModelCatalogDescription], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.', N'SCHEMA', [Production], N'VIEW', [vProductModelInstructions], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Sales representiatives (names and addresses) and their sales-related information.', N'SCHEMA', [Sales], N'VIEW', [vSalesPerson], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Uses PIVOT to return aggregated sales information for each sales representative.', N'SCHEMA', [Sales], N'VIEW', [vSalesPersonSalesByFiscalYears], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Joins StateProvince table with CountryRegion table.', N'SCHEMA', [Person], N'VIEW', [vStateProvinceCountryRegion], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Stores (names and addresses) that sell Adventure Works Cycles products to consumers.', N'SCHEMA', [Sales], N'VIEW', [vStoreWithDemographics], NULL, NULL;
EXEC [sys].[sp_addextendedproperty] N'MS_Description', N'Vendor (company) names and addresses and the names of vendor employees to contact.', N'SCHEMA', [Purchasing], N'VIEW', [vVendor], NULL, NULL;