-- vProductModelCatalogDescription View
CREATE VIEW vProductModelCatalogDescription
AS

select
	ProductModelID,
	Name,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Summary') as Summary,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Manufacturer/Name') as Manufacturer,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Manufacturer/Copyright') as Copyright,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Manufacturer/ProductURL') as ProductURL,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/Warranty/WarrantyPeriod') as WarrantyPeriod,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/Warranty/Description') as WarrantyDescription,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/Maintenance/NoOfYears') as NoOfYears,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/Maintenance/Description') as MaintenanceDescription,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/wheel') as Wheel,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/saddle') as Saddle,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/pedal') as Pedal,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/BikeFrame') as BikeFrame,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/crankset') as Crankset,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Picture/Angle') as PictureAngle,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Picture/Size') as PictureSize,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Picture/ProductPhotoID') as ProductPhotoID,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/Material') as Material,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/Color') as Color,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/ProductLine') as ProductLine,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/Style') as Style,
	XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/RiderExperience') as RiderExperience,
	rowguid,
	ModifiedDate
from ProductModel
where
	catalogdescription is not null; 



-- vGetAllCategories View
CREATE VIEW vGetAllCategories
AS

SELECT
	P.Name AS ParentProductCategoryName,
	C.Name as ProductCategoryName,
	C.ProductCategoryID
FROM (
	SELECT
		ParentProductCategoryID,
		ProductCategoryID,
		Name
	FROM ProductCategory
	WHERE
		ParentProductCategoryID IS NULL
) AS P
LEFT JOIN ProductCategory AS C
	ON P.ProductCategoryID = C.ParentProductCategoryID; 



-- vProductAndDescription View
CREATE VIEW vProductAndDescription
AS

SELECT
	p.ProductID,
	p.Name,
	pm.Name AS ProductModel,
	pmx.Culture,
	pd.Description
FROM Product p
INNER JOIN ProductModel pm
	ON p.ProductModelID = pm.ProductModelID
INNER JOIN ProductModelProductDescription pmx
	ON pm.ProductModelID = pmx.ProductModelID
INNER JOIN ProductDescription pd
	ON pmx.ProductDescriptionID = pd.ProductDescriptionID;