CROSS APPLY with encrypted column converted to XML

Question

I am trying to put this Decompress CLR function to work with CROSS APPLY but so far I had not success. The Decompress functions works fine. The problem is to Decompress the column and call .nodes function as shown below. Please, if anyone could help me with that would be great.

SELECT
T0.Chassis.value('Model', 'varchar(50)')
FROM ANYTABLE c 
CROSS APPLY CAST( dbo.Decompress( CAST(content AS nvarchar(max)) ) AS xml).nodes('this part is correct') AS T0(Chassis)

Answer 1

You need to split your cast to XML and the .nodes part.

SELECT T0.Chassis.value('Model', 'varchar(50)')
FROM ANYTABLE c 
  CROSS APPLY (select CAST( dbo.Decompress( CAST(content AS nvarchar(max)) ) AS xml)) as T(X)
  CROSS APPLY T.X.nodes('this part is correct') AS T0(Chassis)