Sql cast to xml then run .value on cast

Question

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[viewProductExportUpdated2]
AS
SELECT     dbo.ProductCategories.ProductCategoryID AS ProductCode, dbo.ProductCategories.ProductCategoryName AS ProductTitle, 
                      dbo.Products.ShortDescription AS ProductShortDesc, dbo.Products.LongDescription AS ProductLongDesc,
                      CAST(dbo.Products.ProductXML AS XML) AS ProdXml, ProdXml.value('data(Products/PackSize)[1]', 'nvarchar(max)') AS ProductPackSize   
FROM         dbo.ProductCategories INNER JOIN
                      dbo.Products ON dbo.ProductCategories.ProductCategoryID = dbo.Products.ProductCategoryID
WHERE     (dbo.ProductCategories.Deleted = 0)
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

I am trying to cast an ntext column (ProductXML)to xml and then run selects on the case.

but i am getting the following error: Msg 4121, Level 16, State 1, Procedure viewProductExportUpdated2, Line 4 Cannot find either column "ProdXml" or the user-defined function or aggregate "ProdXml.value", or the name is ambiguous.

The site is an old site and we dont have time to change the coloumn to xml etc..

thanks

mike

Answer 1

This is not a CAST to xml. It would be:

CAST(ProdXml /* but the question text says ProductXML */, nvarchar(max))

Now, shredding the XML requires more info, such as a sample and desired output. Perhaps another question, or update this one?

Example XML shred 1

Answer 2

The only part of your query that doesn't use the column alias is the part that is throwing an error, namely:

 ProdXml.value('data(Products/PackSize)[1]', 'nvarchar(max)') AS ProductPackSize

Does changing it to dbo.ProdXml fix the error or generate a different one?