Click here to Skip to main content
15,897,273 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
XML
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>
Posted
Updated 26-Feb-14 18:38pm
v2

Hi , you can fetch data using data adapter to dataset(without FOR XML PATH)


directly you can use dataset function
SQL
dsMyData.WriteXml("D:\1.xml");
 
Share this answer
 
Hi,
Please add
SQL
FOR XML AUTO


in the end of the query.

I hope this will help you.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900