ScrewTurn Wiki
Navigation
Main Page
Random Page
Create a new Page
All Pages
Categories
Administration
File Management
Login/Logout
Language Selection
Your Profile
Create Account
Quick Search
Advanced Search »
Back
History
Sales.vIndividualDemographics_(view)
== wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- valign="top" | '''View''' | Sales.vIndividualDemographics |- valign="top" | '''Description''' | Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table. |} === Source === {{{{<nowiki> 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; </nowiki>}}}} === References === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Dependency Type''' | '''Object Type''' | '''Referenced Object''' |- valign="top" | Select | Table | [[Sales.Individual_(table)|Sales.Individual]] |- valign="top" | Schema | Schema | [[Sales_(schema)|Sales]] |} == automatically generated == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view''' | Sales.vIndividualDemographics |- valign="top" | '''Description''' | Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table. |- |} {{{{<nowiki> 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; </nowiki>}}}} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Dependency Type''' | '''Object Type''' | '''Referenced Object''' |- | Select | Table | [[Sales.Individual_(table)|Sales.Individual]] |- | Schema | Schema | [[Sales_(schema)|Sales]] |}
ScrewTurn Wiki version 2.0.36. Some of the icons created by
FamFamFam
.