View: vPersonDemographics

Properties

Property	Value
Name	vPersonDemographics
ID	1867153697
Schema	Sales
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 element in the xml column Demographics for each customer in the Person.Person table.

View Columns

Column Name	Datatype	Nullable	Is Identity
BusinessEntityID	int
TotalPurchaseYTD	money
DateFirstPurchase	datetime
BirthDate	datetime
MaritalStatus	nvarchar(1)
YearlyIncome	nvarchar(30)
Gender	nvarchar(1)
TotalChildren	int
NumberChildrenAtHome	int
Education	nvarchar(30)
Occupation	nvarchar(30)
HomeOwnerFlag	bit
NumberCarsOwned	int

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 [vPersonDemographics]

Sales.vPersonDemographics

Objects on which [vPersonDemographics] depends

Sales.vPersonDemographics
- Person.Person
  - dbo.Name
  - dbo.NameStyle
  - Demographics.exist
  - Person.AdditionalContactInfoSchemaCollection
  - Person.BusinessEntity
  - Person.IndividualSurveySchemaCollection

SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE VIEW [Sales].[vPersonDemographics] AS SELECT p.[BusinessEntityID] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; TotalPurchaseYTD[1]', 'money') AS [TotalPurchaseYTD] ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; DateFirstPurchase[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [DateFirstPurchase] ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; BirthDate[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [BirthDate] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; MaritalStatus[1]', 'nvarchar(1)') AS [MaritalStatus] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; YearlyIncome[1]', 'nvarchar(30)') AS [YearlyIncome] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; Gender[1]', 'nvarchar(1)') AS [Gender] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; TotalChildren[1]', 'integer') AS [TotalChildren] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; NumberChildrenAtHome[1]', 'integer') AS [NumberChildrenAtHome] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; Education[1]', 'nvarchar(30)') AS [Education] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; Occupation[1]', 'nvarchar(30)') AS [Occupation] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; HomeOwnerFlag[1]', 'bit') AS [HomeOwnerFlag] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; NumberCarsOwned[1]', 'integer') AS [NumberCarsOwned] FROM [Person].[Person] p CROSS APPLY p.[Demographics].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; /IndividualSurvey') AS [IndividualSurvey](ref) WHERE [Demographics] IS NOT NULL; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'VIEW',@level1name=N'vPersonDemographics'

Script

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [Sales].[vPersonDemographics] AS SELECT p.[BusinessEntityID] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; TotalPurchaseYTD[1]', 'money') AS [TotalPurchaseYTD] ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; DateFirstPurchase[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [DateFirstPurchase] ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; BirthDate[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [BirthDate] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; MaritalStatus[1]', 'nvarchar(1)') AS [MaritalStatus] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; YearlyIncome[1]', 'nvarchar(30)') AS [YearlyIncome] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; Gender[1]', 'nvarchar(1)') AS [Gender] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; TotalChildren[1]', 'integer') AS [TotalChildren] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; NumberChildrenAtHome[1]', 'integer') AS [NumberChildrenAtHome] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; Education[1]', 'nvarchar(30)') AS [Education] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; Occupation[1]', 'nvarchar(30)') AS [Occupation] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; HomeOwnerFlag[1]', 'bit') AS [HomeOwnerFlag] ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; NumberCarsOwned[1]', 'integer') AS [NumberCarsOwned] FROM [Person].[Person] p CROSS APPLY p.[Demographics].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; /IndividualSurvey') AS [IndividualSurvey](ref) WHERE [Demographics] IS NOT NULL;
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'VIEW',@level1name=N'vPersonDemographics'