Difference between revisions of "Production.vProductModelInstructions (view)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Production.vProductModelInstructions |- |} <pre> CREATE VIEW [Production].[vPr...)
 
Line 30: Line 30:
 
     step') Steps(ref);
 
     step') Steps(ref);
 
</pre>
 
</pre>
 +
 +
== automatically generated ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|-
 +
| '''view
 +
| 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.
 +
|-
 +
 +
|}
 +
 +
<pre>
 +
 +
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);
 +
</pre>
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Referenced Object
 +
| '''Object Type
 +
| '''Dependency Type
 +
 +
|-
 +
| [[Production.ProductModel_(table)|Production.ProductModel]]
 +
| Table
 +
| Select
 +
|}

Revision as of 23:59, 26 November 2009

wikibot

view Production.vProductModelInstructions 
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);

automatically generated

view 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. 

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


Referenced Object Object Type Dependency Type
Production.ProductModel Table Select
Retrieved from "http://dbshelp.devio.at/index.php?title=Production.vProductModelInstructions_(view)&oldid=1290"