AdventureWorks

Basic Index   Expanded Index

View Name:Production.vProductModelCatalogDescription
Description:Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data.

FieldTypeNulls?
ProductModelIDintNo
Namenvarchar (50)No
Summarynvarchar (MAX)Yes
Manufacturernvarchar (MAX)Yes
Copyrightnvarchar (30)Yes
ProductURLnvarchar (256)Yes
WarrantyPeriodnvarchar (256)Yes
WarrantyDescriptionnvarchar (256)Yes
NoOfYearsnvarchar (256)Yes
MaintenanceDescriptionnvarchar (256)Yes
Wheelnvarchar (256)Yes
Saddlenvarchar (256)Yes
Pedalnvarchar (256)Yes
BikeFramenvarchar (MAX)Yes
Cranksetnvarchar (256)Yes
PictureAnglenvarchar (256)Yes
PictureSizenvarchar (256)Yes
ProductPhotoIDnvarchar (256)Yes
Materialnvarchar (256)Yes
Colornvarchar (256)Yes
ProductLinenvarchar (256)Yes
Stylenvarchar (256)Yes
RiderExperiencenvarchar (1024)Yes
rowguiduniqueidentifierNo
ModifiedDatedatetimeNo

CREATE VIEW [Production].[vProductModelCatalogDescription]
AS
SELECT
[ProductModelID]
,[Name]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace html="http://www.w3.org/1999/xhtml";
(/p1:ProductDescription/p1:Summary/html:p)[1]', 'nvarchar(max)') AS [Summary]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:Name)[1]', 'nvarchar(max)') AS [Manufacturer]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:Copyright)[1]', 'nvarchar(30)') AS [Copyright]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:ProductURL)[1]', 'nvarchar(256)') AS [ProductURL]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Warranty/wm:WarrantyPeriod)[1]', 'nvarchar(256)') AS [WarrantyPeriod]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Warranty/wm:Description)[1]', 'nvarchar(256)') AS [WarrantyDescription]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Maintenance/wm:NoOfYears)[1]', 'nvarchar(256)') AS [NoOfYears]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Maintenance/wm:Description)[1]', 'nvarchar(256)') AS [MaintenanceDescription]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:wheel)[1]', 'nvarchar(256)') AS [Wheel]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:saddle)[1]', 'nvarchar(256)') AS [Saddle]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:pedal)[1]', 'nvarchar(256)') AS [Pedal]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:BikeFrame)[1]', 'nvarchar(max)') AS [BikeFrame]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeature
s";
(/p1:ProductDescription/p1:Features/wf:crankset)[1]', 'nvarchar(256)') AS [Crankset]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:Angle)[1]', 'nvarchar(256)') AS [PictureAngle]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:Size)[1]', 'nvarchar(256)') AS [PictureSize]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:ProductPhotoID)[1]', 'nvarchar(256)') AS [ProductPhotoID]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Material)[1]', 'nvarchar(256)') AS [Material]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Color)[1]', 'nvarchar(256)') AS [Color]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/ProductLine)[1]', 'nvarchar(256)') AS [ProductLine]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Style)[1]', 'nvarchar(256)') AS [Style]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/RiderExperience)[1]', 'nvarchar(1024)') AS [RiderExperience]
,[rowguid]
,[ModifiedDate]
FROM [Production].[ProductModel]
WHERE [CatalogDescription] IS NOT NULL;