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	08/01/2010 08:41
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
ProductModelID	int	4
Name	dbo.Name	50
Instructions	nvarchar	16
LocationID	int	4
SetupHours	decimal	9 (9,4)
MachineHours	decimal	9 (9,4)
LaborHours	decimal	9 (9,4)
LotSize	int	4
Step	nvarchar	1024
rowguid	uniqueidentifier	16
ModifiedDate	datetime	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