Sales.vIndividualDemographics View

Description

Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.

Properties

Creation Date	08/01/2010 08:41
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
CustomerID	int	4
TotalPurchaseYTD	money	8
DateFirstPurchase	datetime	4
BirthDate	datetime	4
MaritalStatus	nvarchar	1
YearlyIncome	nvarchar	30
Gender	nvarchar	1
TotalChildren	int	4
NumberChildrenAtHome	int	4
Education	nvarchar	30
Occupation	nvarchar	30
HomeOwnerFlag	bit	1
NumberCarsOwned	int	4

Objects that Sales.vIndividualDemographics depends on

Database Object	Object Type	Description	Dep Level
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	2
Sales.Customer	Table	Current customer information. Also see the Individual and Store tables.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	4
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	4
dbo.Flag	User Defined Data Type		5
Sales.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	1
dbo.NameStyle	User Defined Data Type		3
dbo.Phone	User Defined Data Type		3
Sales.SalesPerson	Table	Sales representative current information.	3
Sales.SalesTerritory	Table	Sales territory lookup table.	3
Sales.Store	Table	Customers (resellers) of Adventure Works products.	2
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	3
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	3
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	3

View Definition

CREATE VIEW [Sales].[vIndividualDemographics] 
AS 

SELECT 
i.[CustomerID] 
,[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 [Sales].[Individual] i 
CROSS APPLY i.[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;

Related Objects

Sales Schema
AdventureWorks Database