Create table “tblTest” in
sql server
 |
This is how
data looks like in the table
 |
With the
above data available, they want it split into individual values. Searching for
possible solutions, here is the solution query I came up with:
|
DECLARE
@temp TABLE(
ID
INT,
Name
Varchar(MAX)
)
INSERT INTO @temp (ID, Name)
SELECT ID, LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) As Name
FROM ( SELECT ID, CAST('<XMLRoot><RowData>' + REPLACE(Name,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x FROM tblTest) tblTest CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
SELECT * FROM @temp WHERE Name = 'kinjal'
|
Output looks
like below.
 |
Hope this
would help you in your project. Let me know if you are able to find a different
solution to the same problem. The best way to learn is to learn from each
other.
