Production.vProductModelInstructions View

Description

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

Properties

Creation Date	27/10/2017 14:33
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
ProductModelID	Integer	4
Name	dbo.Name	50
Instructions	LongVarWChar	16
LocationID	Integer	4
SetupHours	Numeric	9 (9,4)
MachineHours	Numeric	9 (9,4)
LaborHours	Numeric	9 (9,4)
LotSize	Integer	4
Step	VarWChar	1024
rowguid	GUID	16
ModifiedDate	DBTimeStamp	4

Objects that Production.vProductModelInstructions depends on

	Database Object	Object Type	Description	Dep Level
	dbo.Name	User Defined Data Type		2
	Production.ProductModel	Table	Product model classification.	1

View Definition

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);

Related Objects