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...)
 
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== wikibot ==
 
== wikibot ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- valign="top"
 +
| '''View
 +
| Production.vProductModelInstructions
 +
|- valign="top"
 +
| '''Description
 +
| Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.
 +
|}
 +
 +
=== Source ===
 +
<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>
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Production.ProductModel_(table)|Production.ProductModel]]
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 +
|}
 +
 +
 +
== automatically generated ==
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
Line 5: Line 60:
 
| '''view
 
| '''view
 
| Production.vProductModelInstructions
 
| Production.vProductModelInstructions
 +
|- valign="top"
 +
| '''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>
 
<pre>
 +
 
CREATE VIEW [Production].[vProductModelInstructions]  
 
CREATE VIEW [Production].[vProductModelInstructions]  
 
AS  
 
AS  
Line 30: Line 89:
 
     step') Steps(ref);
 
     step') Steps(ref);
 
</pre>
 
</pre>
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Production.ProductModel_(table)|Production.ProductModel]]
 +
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 +
|}

Latest revision as of 23:17, 23 June 2010

wikibot[edit]

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.

Source[edit]

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

References[edit]

Dependency Type Object Type Referenced Object
Select Table Production.ProductModel
Schema Schema Production


automatically generated[edit]

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


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