Click here to Skip to main content
14,240,435 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Rate this:
Please Sign up or sign in to vote.

Solution 1

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


directly you can use dataset function
dsMyData.WriteXml("D:\1.xml");
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Hi,
Please add
FOR XML AUTO


in the end of the query.

I hope this will help you.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100