Description
Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.
Properties
| Creation Date | 27/10/2017 14:33 |
| Is Schema Bound |  |
| Encrypted | |
| Ansi Nulls |  |
Columns
| | Column Name | Description | Datatype | Length | Allow Nulls | Default | Formula |
|---|
| | BusinessEntityID | | Integer | 4 | | | |
| | TotalPurchaseYTD | | Currency | 8 |  | | |
| | DateFirstPurchase | | DBTimeStamp | 4 | | | |
| | BirthDate | | DBTimeStamp | 4 | | | |
| | MaritalStatus | | VarWChar | 1 | | | |
| | YearlyIncome | | VarWChar | 30 | | | |
| | Gender | | VarWChar | 1 | | | |
| | TotalChildren | | Integer | 4 | | | |
| | NumberChildrenAtHome | | Integer | 4 | | | |
| | Education | | VarWChar | 30 | | | |
| | Occupation | | VarWChar | 30 | | | |
| | HomeOwnerFlag | | Boolean | 1 | | | |
| | NumberCarsOwned | | Integer | 4 | | | |
Objects that Sales.vPersonDemographics depends on
| | Database Object | Object Type | Description | Dep Level |
|---|
 | Person.BusinessEntity | Table | Source of the ID that connects vendors, customers, and employees with address and contact information. | 2 |
 | dbo.Name | User Defined Data Type | | 2 |
| dbo.NameStyle | User Defined Data Type | | 2 |
| Person.Person | Table | Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts. | 1 |
View Definition
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;
|
See Also