Latest revision |
Your text |
Line 1: |
Line 1: |
| == wikibot == | | == 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 ===
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | === 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" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
Line 72: |
Line 5: |
| | '''view | | | '''view |
| | Sales.vIndividualDemographics | | | Sales.vIndividualDemographics |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.
| |
| |- | | |- |
| |} | | |} |
| | | |
| <pre> | | <pre> |
− |
| |
| CREATE VIEW [Sales].[vIndividualDemographics] | | CREATE VIEW [Sales].[vIndividualDemographics] |
| AS | | AS |
Line 113: |
Line 42: |
| WHERE [Demographics] IS NOT NULL; | | WHERE [Demographics] IS NOT NULL; |
| </pre> | | </pre> |
− |
| |
− |
| |
− | {| 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]]
| |
− |
| |
− | |}
| |