problem with bcp to export to xml

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

problem with bcp to export to xml

Author

Topic

leunam013
Starting Member

1 Post

Posted - 2013-01-15 : 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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 10:48:40

BCP gives you one string with no formatting. If you open it in any XML editor you should be able to format it assuming the XML is well-formed (the second sample you posted is not)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-15 : 12:19:37

Even opening .xml file in internet explorer will format it correctly based on nodes
I hope you didnt post entire XML as both the XML posted above is not complete and well formed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources