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. |
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 |
Objects that [Production].[vProductModelInstructions] depends on
Object Name | Owner | Object Type | Dep Level | |
Name | dbo | User Defined type | 1 | |
ProductModel | Production | Table | 2 |
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