SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 problem with bcp to export to xml
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leunam013
Starting Member

1 Posts

Posted - 01/15/2013 :  10:23:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 01/15/2013 :  10:48:40  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/15/2013 :  12:19:37  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000