CODEWITHSUNDEEP
sql-serverxmlbcp
leunam013
leunam013

Reputation: 9

XML output not formatted when written to file with bcp

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.

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 output

<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 output 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>[email protected]</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 all the XML format is lost, how do I keep it?

Upvotes: 1

Views: 1551

Answers (1)

Ben
Ben

Reputation: 21

The BCP utility does not keep the formatting of XML files, to format and indent the file use a program such as notepad++ to 'pretty print' the xml file.

Upvotes: 2

Related Questions