|
leunam013
Starting Member
1 Posts |
 Posted - 01/15/2013 : 10:23:57
|
good day,
i have query that create an xml in sql server and works fine, the problem is that i need to save it to a file . when i do that the format get corrupted
query
--------------------------------------------------------------
SELECT mydata.dbo.tblBooking.ID, mydata.dbo.tblBooking.ReservationID, mydata.dbo.tblBooking.EventName, mydata.dbo.tblBooking.TimeEventStart, mydata.dbo.tblBooking.TimeEventEnd, mydata.dbo.tblRoom.Room,
mydata.dbo.tblReservation.TempContact, mydata.dbo.tblGroup.GroupName, mydata.dbo.tblUserDefinedField.FieldValue, mydata.dbo.tblEventType.Description
FROM
mydata.dbo.tblBooking INNER JOIN mydata.dbo.tblReservation ON mydata.dbo.tblBooking.ReservationID = mydata.dbo.tblReservation.ID
INNER JOIN mydata.dbo.tblEventType ON mydata.dbo.tblBooking.EventTypeID = mydata.dbo.tblEventType.ID
INNER JOIN mydata.dbo.tblGroup ON mydata.dbo.tblReservation.GroupID = mydata.dbo.tblGroup.ID
INNER JOIN mydata.dbo.tblUserDefinedField ON mydata.dbo.tblBooking.ReservationID = mydata.dbo.tblUserDefinedField.ParentID
INNER JOIN mydata.dbo.tblRoom ON mydata.dbo.tblBooking.RoomID = mydata.dbo.tblRoom.ID
where mydata.dbo.tblBooking.TimeEventStart between '2012-11-1' and '2012-12-20'
for xml Raw ,elements , ROOT ('EMSExport')
-------------------------
this is the out put
--------------------------------
<EMSExport>
<row>
<ID>10891</ID>
<ReservationID>431</ReservationID>
<EventName>Bowery Mission Trip Wrap Party</EventName>
<TimeEventStart>2012-11-08T17:45:00</TimeEventStart>
<TimeEventEnd>2012-11-08T19:30:00</TimeEventEnd>
<Room>6-Conference </Room>
<TempContact>Keith Yagnik</TempContact>
<GroupName>Outreach</GroupName>
<FieldValue></FieldValue>
<Description>Meeting</Description>
</row>
<row>
<ID>10894</ID>
<ReservationID>434</ReservationID>
<EventName>SBJ Meeting</EventName>
<TimeEventStart>2012-11-06T11:45:00</TimeEventStart>
<TimeEventEnd>2012-11-06T12:30:00</TimeEventEnd>
<Room>6-Conference </Room>
<TempContact></TempContact>
<GroupName>Staff</GroupName>
<FieldValue></FieldValue>
<Description>Meeting</Description>
</row>
<row>
<ID>10888</ID>
<ReservationID>428</ReservationID>
<EventName>PCUSA missionary Cobbie Palm</EventName>
<TimeEventStart>2012-11-04T09:30:00</TimeEventStart>
<TimeEventEnd>2012-11-04T10:30:00</TimeEventEnd>
<Room>5-Corning Logan </Room>
<TempContact>Kate Dunn</TempContact>
<GroupName>Adult Education</GroupName>
<FieldValue></FieldValue>
<Description>Event</Description>
</row>
**********************************************
this is the procedure to save the file
-------------------------------------------------------------
declare @cmd nvarchar(2000);
select @cmd = 'bcp "EXEC EMSXMLraw" queryout "C:\testraw.xml" -x -c -t, -T -S MYshel\EM'
exec xp_cmdshell @cmd
this is the format of the file
------------------------------------------
<EMSExport><row><ID>10891</ID><ReservationID>431</ReservationID><EventName>Bowery Mission Trip Wrap Party</EventName><TimeEventStart>2012-11-08T17:45:00</TimeEventStart><TimeEventEnd>2012-11-08T19:30:00</TimeEventEnd><Room>6-Conference </Room><TempContact>Keith Yagnik</TempContact><GroupName>Outreach</GroupName><FieldValue></FieldValue><Description>Meeting</Description></row><row><ID>10894</ID><ReservationID>434</ReservationID><EventName>SBJ Meeting</EventName><TimeEventStart>2012-11-06T11:45:00</TimeEventStart><TimeEventEnd>2012-11-06T12:30:00</TimeEventEnd><Room>6-Conference </Room><TempContact></TempContact><GroupName>Staff</GroupName><FieldValue></FieldValue><Description>Meeting</Description></row><row><ID>10888</ID><ReservationID>428</ReservationID><EventName>PCUSA missionary Cobbie Palm</EventName><TimeEventStart>2012-11-04T09:30:00</TimeEventStart><TimeEventEnd>2012-11-04T10:30:00</TimeEventEnd><Room>5-Corning Logan </Room><TempContact>Kate Dunn</TempContact><GroupName>Adult Education</GroupName><FieldValue></FieldValue><Description>Event</Description></row><row><ID>10263</ID><ReservationID>372</ReservationID><EventName>AA Christmas Lunch </EventName><TimeEventStart>2012-12-13T11:30:00</TimeEventStart><TimeEventEnd>2012-12-13T14:30:00</TimeEventEnd><Room>2-Bonnell </Room><TempContact>jimenez@amexclusive.com</TempContact><GroupName>Alcoholics Anonymous </GroupName><FieldValue>THISISTHEWEBDESCRIPTION</FieldValue><Description>Event</Description></row><row><ID>10817</ID><ReservationID>391</ReservationID><EventName>E.S.N. General Meeting</EventName><TimeEventStart>2012-11-13T18:30:00</TimeEventStart><TimeEventEnd>2012-11-13T20:30:00</TimeEventEnd><Room>3-Jones </Room><TempContact></TempContact><GroupName>FAPC</GroupName><FieldValue>Emergency Network of Shelters General Meeting</FieldValue>
as you can see lost all the XML format
please help |
|
problem with bcp to export to xml
