View: Sales.vIndividualDemographics

Properties

Owner:	Sales
Schema bound:
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.

Columns

Name	Data Type	Length	NULL	IsGUID	Description
CustomerID	int	4
TotalPurchaseYTD	money	8
DateFirstPurchase	datetime	8
BirthDate	datetime	8
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

Total: 13 column(s)

Objects that [Sales].[vIndividualDemographics] depends on

Object Name	Owner	Object Type	Dep Level
Name	dbo	User Defined type	1
NameStyle	dbo	User Defined type	1
Phone	dbo	User Defined type	1
ufnLeadingZeros	dbo	Function	2
Contact	Person	Table	2
SalesTerritory	Sales	Table	2
Customer	Sales	Table	3
Individual	Sales	Table	4

Total: 8 objects

SQL

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;