AdventureWorks

Basic Index   Expanded Index

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

FieldTypeNulls?
ProductModelIDintNo
Namenvarchar (50)No
Instructionsnvarchar (MAX)Yes
LocationIDintYes
SetupHoursdecimal (9, 4)Yes
MachineHoursdecimal (9, 4)Yes
LaborHoursdecimal (9, 4)Yes
LotSizeintYes
Stepnvarchar (1024)Yes
rowguiduniqueidentifierNo
ModifiedDatedatetimeNo

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