[Sales].[vPersonDemographics]
Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.
| Property | Value |
|---|---|
| Collation | SQL_Latin1_General_CP1_CI_AS |
| ANSI Nulls On | True |
| Quoted Identifier On | True |
| Created | 13:14:55 14 marca 2012 |
| Last Modified | 13:14:55 14 marca 2012 |
| Name | Data Type | Max Length (Bytes) |
|---|---|---|
| BusinessEntityID | int | 4 |
| TotalPurchaseYTD | money | 8 |
| DateFirstPurchase | datetime | 8 |
| BirthDate | datetime | 8 |
| MaritalStatus | nvarchar(1) | 2 |
| YearlyIncome | nvarchar(30) | 60 |
| Gender | nvarchar(1) | 2 |
| TotalChildren | int | 4 |
| NumberChildrenAtHome | int | 4 |
| Education | nvarchar(30) | 60 |
| Occupation | nvarchar(30) | 60 |
| HomeOwnerFlag | bit | 1 |
| NumberCarsOwned | int | 4 |
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;
GO
EXEC sp_addextendedproperty N'MS_Description', N'Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.', 'SCHEMA', N'Sales', 'VIEW', N'vPersonDemographics', NULL, NULL
GO