Production.vProductModelInstructions

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	Production
Name	vProductModelInstructions
Module	Products

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

Columns

		Name	Data type	Attributes
	1	ProductModelID	int	Identity
Identity
	2	Name	nvarchar(50)
	3	Instructions	nvarchar(MAX)
	4	LocationID	int
	5	SetupHours	decimal(9, 4)
	6	MachineHours	decimal(9, 4)
	7	LaborHours	decimal(9, 4)
	8	LotSize	int
	9	Step	nvarchar(1024)
	10	rowguid	uniqueidentifier
	11	ModifiedDate	datetime

Uses

Name
Production.vProductModelInstructions
Production.ProductModel
MfgInstructions.ref.value
Steps.ref.value

Script

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

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

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56