none
problem using bcp to export to xml file RRS feed

  • Question

  • 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

    the problem is for example if i try to open it 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


    please help

    • Edited by leunam013 Wednesday, January 16, 2013 4:22 PM
    Wednesday, January 16, 2013 4:20 PM

Answers

  • Thanks for the answers

    I found the problem:

    The break in the line was caused for empty fields in the table

    This is the solution

    for xml Raw ('description') ,TYPE , elements xsinil , ROOT ('Export')

    Thursday, January 17, 2013 7:30 PM

All replies

  • Try using the -w switch instead of the -c in order to bcp out xml, eg

    bcp "SELECT name FROM tempdb.sys.objects FOR XML RAW" queryout "c:\temp\temp.xml" -S.\sql2012 -T -w

    Wednesday, January 16, 2013 5:08 PM
    Answerer
  • Or you Can try with SQLCMD like below...

    EXECUTE master..xp_cmdshell 'SqlCmd   -S SAURABHBHADURIA -d master  -E  -i d:\query.sql  -h -1 -y 0 -o d:\out.xml '
    
    

    in d:\query.sql File I have save my query ..which is below..

     set nocount on
    :XML ON
      SELECT top(50) name,type FROM master.sys.objects s for xml Raw ,elements , ROOT ('EMSExport')

    Thanks,

    Saurabh


    http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

    Thursday, January 17, 2013 8:41 AM
  • Thanks for the answers

    I found the problem:

    The break in the line was caused for empty fields in the table

    This is the solution

    for xml Raw ('description') ,TYPE , elements xsinil , ROOT ('Export')

    Thursday, January 17, 2013 7:30 PM