SQL (Server) XML cast question - Error XMLDT method 'nodes' can only be invoked on columns-CodePudding

I am trying to access XML data attributes that are in an Navchar column

A column with data:


<Summary><Item><Name>Test22</Name><Value>3.50</Value></Item></Summary>

A column without data:
</Summary>

Name is Discount Name and Value is Discount Value

This information comes from an OrderItem table stored on SQL Server

I try this query to extract Order Information from the COM_OrderItem table

SELECT OrderID AS [Order No], OrderItemUnitCount As [Units], OrderItemSKUName As [Product], OrderItemUnitPrice As [Unit Price], OrderItemTotalPrice As [Line Total],
CAST(OrderItemProductDiscounts as XML) As CatDiscounts, Child.discountName AS [DiscountName], Child.discountValue AS [DiscountValue],
FROM COM_OrderItem

CROSS APPLY
 
(
 
       SELECT Summary.c.value('Name[1]','varchar(max)') AS discountName
 
       ,Summary.c.value('Value[1]','decimal(18,2)') AS discountValue
 
       FROM CatDiscounts.nodes('/Summary/Item') Summary (c)
 
) Child

I get the following error:

Child Caused exception: Invalid column name 'CatDiscounts'. The XMLDT method 'nodes' can only be invoked on columns of type xml.

It seems the CatDiscounts column name is the problem.

However, I am not sure how to change the query to fix this.

Any help would be appreciated (am a newbie at SQL).

Tried many different combinations of the above code

Tried using SET on the CatDiscounts column name and got syntax errors

CodePudding user response：

You need to cast the column to XML and then to apply .nodes() function.

Try this:

WITH DataSource AS
(
    SELECT OrderID AS [Order No]
         , OrderItemUnitCount As [Units]
         , OrderItemSKUName As [Product]
         , OrderItemUnitPrice As [Unit Price]
         , OrderItemTotalPrice As [Line Total]
         ,CAST(OrderItemProductDiscounts as XML) As CatDiscounts
         , Child.discountName AS [DiscountName]
         , Child.discountValue AS [DiscountValue]
    FROM COM_OrderItem
)

SELECT OrderID AS [Order No]
     , OrderItemUnitCount As [Units]
     , OrderItemSKUName As [Product]
     , OrderItemUnitPrice As [Unit Price]
     , OrderItemTotalPrice As [Line Total]
     , CatDiscounts As [CatDiscounts]
     , Child.discountName AS [DiscountName]
     , Child.discountValue AS [DiscountValue]
FROM DataSource
CROSS APPLY
(
       SELECT Summary.c.value('Name[1]','varchar(max)') AS discountName
       ,Summary.c.value('Value[1]','decimal(18,2)') AS discountValue
 
       FROM CatDiscounts.nodes('/Summary/Item') Summary (c)
 
) Child;

If you look at Logical Processing Order of the SELECT statement you can see that the FROM, JOIN (and cross apply) are executed before the SELECT statement.

So, you are converting the OrderItemProductDiscounts to XML in the SELECT but in the CROSS APPLY it is still not converted and .nodes function cannot be applied.