i output should be in the below format i have eventid, date, time, duration, name ,synopsis as columns in my database i tried to covert in sql server 2005 i used two coloums for test select (Select cast(service_ID as nvarchar) +cast(Year(Convert(datetime,date,103))as nvarchar) +Substring(date,Charindex('/',date)+1,2) +Substring(date,Charindex('/',date)-2,2) -- +cast(datepart(dd,Convert(datetime,date,103))as nvarchar) +'E' +cast((DENSE_RANK() OVER (PARTITION BY date ORDER BY frmtime ASC))as nvarchar) as EVENTID from PRG_detail where service_ID=101 FOR XML PATH(''), TYPE), (SELECT A.DATE,A.frmtime as TIME FROM prg_detail A FOR XML PATH('START'), TYPE) FOR XML PATH(''), ROOT('EVENTSECTION') but output is event id first and the start division like this <EVENTSECTION> <EVENTID>10120140206E1</EVENTID> <EVENTID>10120140206E2</EVENTID> <EVENTID>10120140206E3</EVENTID> <EVENTID>10120140206E4</EVENTID> <EVENTID>10120140206E5</EVENTID> <EVENTID>10120140206E6</EVENTID> <EVENTID>10120140206E7</EVENTID> <EVENTID>10120140206E8</EVENTID> <EVENTID>10120140206E9</EVENTID> <EVENTID>10120140206E10</EVENTID> <START> <DATE>06/02/2014</DATE> <TIME>00:00:00</TIME> </START> <START> <DATE>06/02/2014</DATE> <TIME>01:30:00</TIME> </START> how to rectify this OUTPUT TO COME Collapse | Copy Code <EVENT_SECTION> <EVENTID>47920140220E1</EVENTID> <START> <DATE>2014/02/20</DATE> <TIME>00:00:00</TIME> </START> <EPG_SECTION> <DURATION>00:30:00</DURATION> <EPG Language="Eng"> <NAME>Comedy Express</NAME> <SYNOPSIS>The program features a compilation of comic scenes from popular films.</SYNOPSIS> <LOG_LINE>Comedy Express</LOG_LINE> </EPG> <THEME>1</THEME> <RATING Country="IND">0</RATING> </EPG_SECTION> </EVENT_SECTION>
dsMyData.WriteXml("D:\1.xml");
FOR XML AUTO
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)