I think your table data structure is ...
id specialities
-------------------------------------------------------------------------
1 Ayurvedic Consultant -- 100,Cancer--200,Cardiac Problem -- 500
2 Something -- 120,Somthing --130,Something -- 140
---------------------------------------------------------------------------
You have construct XML object by using following query
WITH SPECCTE AS (
SELECT
CAST('<i Speciality="'+ REPLACE( REPLACE(Specialities,'--','" Cost="'),',','"></i><i Speciality="') +'"></i>' AS XML) XMLSPECS
FROM tblspecialities
)
SELECT * FROM SPECCTE
If you execute above code block, follwing XML result will be returned
<i Speciality="Ayurvedic Consultant " Cost=" 100" /><i Speciality="Cancer" Cost="200" /><i Speciality="Cardiac Problem " Cost=" 500" />
To get the result like this..
Speciality Cost
-----------------------------------------
Ayurvedic Consultant 100
Cancer 200
Cardiac Problem 500
-----------------------------------------
We need to split the XML result using XQuery like following method..
WITH SPECCTE AS (
SELECT
CAST('<i Speciality="'+ REPLACE( REPLACE(Specialities,'--','" Cost="'),',','"></i><i Speciality="')
+'"></i>' AS XML) XMLSPECS
FROM tblspecialities
)
SELECT LTRIM(RTRIM(x.i.value('@Speciality[1]','VARCHAR(100)'))) Speciality,
LTRIM(RTRIM(x.i.value('@Cost[1]','VARCHAR(10)'))) Cost FROM SPECCTE
CROSS APPLY XMLSPECS.nodes('//i') x(i)
If we need to get a record for specific speciality, then we need to run this code block.
DECLARE @SrchSpeciality VARCHAR(50) = 'Cardiac Problem';
WITH SPECCTE AS (
SELECT
CAST('<i Speciality="'+ REPLACE( REPLACE(Specialities,'--','" Cost="'),',','"></i><i Speciality="')
+'"></i>' AS XML) XMLSPECS
FROM tblspecialities WHERE Specialities LIKE '%'+@SrchSpeciality+'%'
)
SELECT LTRIM(RTRIM(x.i.value('@Speciality[1]','VARCHAR(100)'))) Speciality,
LTRIM(RTRIM(x.i.value('@Cost[1]','VARCHAR(10)'))) Cost FROM SPECCTE
CROSS APPLY XMLSPECS.nodes('//i') x(i)
WHERE x.i.value('@Speciality[1]','VARCHAR(100)') LIKE '%'+@SrchSpeciality+'%'
The result of above code block is
Speciality Cost
-------------------------------
Cardiac Problem 500
-------------------------------
Thank you ..