xml - how to split a single row in to multiple rows in SQL 2008 -

xml - how to split a single row in to multiple rows in SQL 2008 -

i have values in single row separated delimiter, single in row in info base. when retrieving values values multiple rows based on delimiter exist in row.

following query :

select ( select rtrim(isnull(t1.[cimtrek_keywords],'')) '@cimtrek_keywords' (select nullif(cd.[cimtrek_keywords],'') cimtrek_keywords cimtrek_systemtable_datawarehouse cd len(ltrim(rtrim(isnull(ltrim(rtrim(cd.[cimtrek_keyfield])),'')))) != 0 , cimtrek_keyfield='cer type') t1 xml path('record'), type ) xml path('carriercode_destination'), type

and next result get.

<carriercode_destination> <record cimtrek_keywords="bucket - minor project|bucket - major project|budget - named project|addfund - named project|already approved|admin" /> </carriercode_destination>

but want keywords come multiple row in above case 5 rows, because later on have bring these values in combo box.

please help me this.

best regards

you can utilize this function. returns table of splitted values, based on input string , delimeter.

usage:

select * dbo.fn_parsetext2table('bucket - minor project|bucket - major project|budget - named project|addfund - named project|already approved|admin','|')

output:

position int_value num_value txt_value 1 (null) (null) bucket - minor project 2 (null) (null) bucket - major project 3 (null) (null) budget - named project 4 (null) (null) addfund - named project 5 (null) (null) approved 6 (null) (null) admin

i've prepared sqlfiddle you: http://sqlfiddle.com/#!3/d41d8/9030

sql xml sql-server-2008 xquery