0

My SQL Server database has entire csv file's data saved in varchar field like in following sample:

StudentName,Field1,Field2,...,Fieldn
Homer,somevalue,somevalue,...,somevalue
Elmer,somevalue,somevalue,...,somevalue

I want to retrieve the data and convert it to html script given as in following sample:

<grade>
    <StudentName name='Homer'>
    </StudentName>
    <StudentName name='Elmer'>
    </StudentName>
</grade>

Can I do this directly using SQL select statement? If not then what is the most efficient way to do it?

Ashutosh Nigam
  • 868
  • 8
  • 27
Denver Naidoo
  • 142
  • 12
  • dupli : http://stackoverflow.com/questions/6328010/how-to-convert-data-base-data-to-xml-file – Tharif Mar 25 '15 at 10:16
  • @a_horse_with_no_name SQL Server – Denver Naidoo Mar 25 '15 at 11:04
  • You could try using many existing SQL Server 'split' functions like ones mentioned here: http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql. Then you could use `FOR XML` to convert that XML. Try that approach and post back any specific issues. Why do you want to do this? – Nick.Mc Mar 25 '15 at 11:27
  • Im having difficulty converting the csv string above into a table – Denver Naidoo Mar 25 '15 at 12:55

1 Answers1

1
select concat('<grade>',t1.stmt,'</grade>' from (select  concat('<StudentName name=''', t.studentname,
              '''> </StudentName>' ) as stmt
from MytableName t) t1

NB: you can use any mechanism to concatenate the result and replace concat with that.

Update:

If your each row is saved in different field then you can get name using charindex and left of t-sql

Ashutosh Nigam
  • 868
  • 8
  • 27