problem using bcp to exporto to xml bad format

Question

problem using bcp to exporto to xml bad format

mgutierrez 97595

Valued Member

Points: 50
More actions
January 15, 2013 at 8:29 am

#269351

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 1

It looks fine to me. All the elements seem to be there. Not sure what you mean by missing XML format.

If you're referring to not being laid out in neat nodes up and down make sure you look at the file in an app that will recognize xml and lay it out nicely for you like IE or something. Notpad will not lay it out for you neatly.

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

Lowell SSC Guru Points: 323518 More actions · Answer 2

Want a cool sig is correct. what was produced is valid XML, just not prettified for readability.

SQL2000 used to have trace flag 257, which would format the xml with extra line breaks, but it doesn't work in any of my 2005 and above installations; a search for trace flags didn't turn up a later trace flag replacement ,either.

If you want it prettier, you could replace </ with CrLf & </ i suppose, but if you want it indented, you need to run it through an application that formats it nicely.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mgutierrez 97595 Valued Member Points: 50 More actions · Answer 3

thanks for the answer.

the problem is for example if i try to open ir with ie i get this error

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

A name contained an invalid character. Error processing resource 'file:///C:/test10000.xml'. Line ...

rvationID>372</ReservationID><EventName>AA Christmas Lunch </EventName><TimeEventStart>2012-12-13...

and i need this in a good XML format because this is going to be read by another program

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 4

You'll have to provide the full xml file since the sample snippet you provided in your initial post works fine once I add the end tags. There's really not enough information here to work with. All I can say is what the error message said. There's a character that it doesn't like somewhere in the file.

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D