parsing sql CAST xml

Question

I have a table with an xml column. i want to parse it for a specific keys in the xml,which i accomplish by doing the following:

SELECT TOP 1000 
 CAST([PlatformInfo] as xml).value('(/PlatformInfo/@Device)[1]', 'varchar(max)')  as Device ,
 CAST([PlatformInfo] as xml).value('(/PlatformInfo/@SDKv)[1]', 'varchar(max)')  as SDKv 
FROM [myDB].[dbo].[HISTORY_TB]

Now i want to sort it and i always get an error. How can i sort the custom columns that i created as Device and SDKv ? How can i sort it by a specific value?

Thanks

Answer 1

Create table #companyname(name varchar(max))
Insert into #companyname (name) values
('<name>US Concrete Inc</name>'),
('<name>Digitiliti Inc</name>'),
('<name>Printron Inc</name>'),
('<name>Wesco Financial, LLC</name>'),
('<name>Fusion Restaurant Group Inc</name>')

select * from (
select top 4 CAST(name as xml).value('(/name)[1]','nvarchar(max)') as t 
from #companyname )tbl
order by tbl.t

Similarly :

 Select * from (
    SELECT TOP 1000 
CAST([PlatformInfo] as xml).value('(/PlatformInfo/@Device)[1]', 'varchar(max)')  as Device ,
CAST([PlatformInfo] as xml).value('(/PlatformInfo/@SDKv)[1]', 'varchar(max)')  as SDKv 
FROM [myDB].[dbo].[HISTORY_TB]) dertbl
order by dertbl.Device,dertbl.SDKv