Properties

Owner:	Production
Schema bound:
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	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	Length	NULL	IsGUID	Description
	ProductModelID	int	4
	Name	Name	100
	Instructions	nvarchar	-0.5
	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)

Objects that [Production].[vProductModelInstructions] depends on

	Object Name	Owner	Object Type	Dep Level
	Name	dbo	User Defined type	1
	ProductModel	Production	Table	2

Total: 2 objects

SQL

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

See Also

List of views