View: [AdventureWorks2016CTP3].[Production].[vProductModelInstructions]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [Production] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Is schema bound   
 Encrypted   
 ID   1197247320 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Resultset


Name  Description Data type Max length
ProductModelID   int 4
Name   [dbo].[Name] 50
Instructions   nvarchar 1073741823
LocationID   int 4
SetupHours   decimal 5
MachineHours   decimal 5
LaborHours   decimal 5
LotSize   int 4
Step   nvarchar 1024
rowguid   uniqueidentifier 16
ModifiedDate   datetime 8
Total: 11 column(s)

Collapse image Objects that [Production].[vProductModelInstructions] depends on


Object name Object type Dep level
[Production] Schema 1
[Production].[ProductModel] Table 1
Total 2 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [Production].[vProductModelInstructions] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[Instructions].value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
        (/root/text())[1]', 'nvarchar(max)') AS [Instructions] 
    ,[MfgInstructions].ref.value('@LocationID[1]', 'int') AS [LocationID] 
    ,[MfgInstructions].ref.value('@SetupHours[1]', 'decimal(9, 4)') AS [SetupHours] 
    ,[MfgInstructions].ref.value('@MachineHours[1]', 'decimal(9, 4)') AS [MachineHours] 
    ,[MfgInstructions].ref.value('@LaborHours[1]', 'decimal(9, 4)') AS [LaborHours] 
    ,[MfgInstructions].ref.value('@LotSize[1]', 'int') AS [LotSize] 
    ,[Steps].ref.value('string(.)[1]', 'nvarchar(1024)') AS [Step] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
CROSS APPLY [Instructions].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    /root/Location') MfgInstructions(ref)
CROSS APPLY [MfgInstructions].ref.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    step') Steps(ref);
GO
EXEC sp_addextendedproperty N'MS_Description', N'Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.', 'SCHEMA', N'Production', 'VIEW', N'vProductModelInstructions', NULL, NULL
GO

Collapse image See also


List of views