XML XQUERY Problem with NTEXT data type

Question

Hello

I want to use XQuery on a column of data type NTEXT (I have no choice!). I have tried converting the column to XML using CONVERT but it gives the error:

Incorrect syntax near the keyword 'CONVERT'.

Here's the query

SELECT 
    y.item.value('@UserID', 'varchar(50)') AS UnitID,   
    y.item.value('@ListingID', 'varchar(100)') AS @ListingID  
FROM   
    dbo.KB_XMod_Modules
    CROSS APPLY     
    CONVERT(xml, instancedata).nodes('//instance') AS y(item)

(instancedata is my column)

Can anyone think of a work around for this ?

Thanks

Answer 1

It seems that neither CONVERT nor CAST(... AS XML) work.

The XQuery stuff really only works on XML columns - sorry.

However: maybe there's an escape route :-)

You could do one of two things:

• add a persisted computed column of type XML to your table and party on that new column

or - if you can't change the table structure -

• create a view over that table and include a XML-casted column

So for solution #1 you'd do:

ALTER TABLE KB_XMod_Modules 
   ADD XmlCol AS CAST(instancedata AS XML) PERSISTED

and for solution #2, you'd use:

CREATE VIEW KBwithXML AS
  SELECT instancedata, CAST(instancedata AS XML) 'XmlCol'
  FROM KB_XMod_Modules

In both cases, you now have a XmlCol of type XML available, which you can query with XQuery to your heart's delight!