Owner: Production 
Schema bound:  
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       
  Name  Name  100       
  Instructions  nvarchar  -0.5       
  LocationID  int       
  SetupHours  decimal       
  MachineHours  decimal       
  LaborHours  decimal       
  LotSize  int       
  Step  nvarchar  1024       
  rowguid  uniqueidentifier  16       
  ModifiedDate  datetime       
Total: 11 column(s)

Objects that [Production].[vProductModelInstructions] depends on

Object Name Owner Object Type Dep Level
  Name  dbo  User Defined type 
  ProductModel  Production  Table 
Total: 2 objects


CREATE VIEW [Production].[vProductModelInstructions]
    ,[Instructions].value(N'declare default element namespace "";
        (/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]
FROM [Production].[ProductModel]
CROSS APPLY [Instructions].nodes(N'declare default element namespace "";
    /root/Location') MfgInstructions(ref)
CROSS APPLY [MfgInstructions].ref.nodes('declare default element namespace "";
    step') Steps(ref);

See Also

List of views