Difference between revisions of "Sales.vIndividualDemographics (view)"
| Line 3: | Line 3: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- | |- | ||
| − | | ''' | + | | '''View |
| Sales.vIndividualDemographics | | Sales.vIndividualDemographics | ||
|- valign="top" | |- valign="top" | ||
| Line 11: | Line 11: | ||
|} | |} | ||
| + | |||
| + | === Source === | ||
<pre> | <pre> | ||
CREATE VIEW [Sales].[vIndividualDemographics] | CREATE VIEW [Sales].[vIndividualDemographics] | ||
| Line 46: | Line 48: | ||
</pre> | </pre> | ||
| − | + | === References === | |
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" | ||
| Line 57: | Line 59: | ||
| Table | | Table | ||
| [[Sales.Individual_(table)|Sales.Individual]] | | [[Sales.Individual_(table)|Sales.Individual]] | ||
| + | |||
| + | |- | ||
| + | | Schema | ||
| + | | Schema | ||
| + | | [[Sales_(schema)|Sales]] | ||
|} | |} | ||
| − | |||
Revision as of 23:12, 2 February 2010
wikibot
| View | Sales.vIndividualDemographics |
| Description | Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table. |
Source
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;
References
| Dependency Type | Object Type | Referenced Object |
| Select | Table | Sales.Individual |
| Schema | Schema | Sales |
automatically generated
| view | Sales.vIndividualDemographics |
| Description | Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table. |
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;
| Dependency Type | Object Type | Referenced Object |
| Select | Table | Sales.Individual |
| Schema | Schema | Sales |
