XML results are seperated into lines of 2033 characters with ODBC

  • I am using FOR XML EXPLICIT in a sql query.  I am returning my results into a file.  The problem I am experiencing is when I go to view the xml file in Internet Explorer.  IE truncates the text in the file after it hits 2033 characters resutling in an error.  Does anyone know of a solution?

    Thanks in advance!

  • Had the same problem...

    export the data in a data stream - ado or bcp work.

  • ADO streams are the way to go - below is VB6 code to do this

    Dim cn As New ADODB.Connection

    Dim cmd As New ADODB.Command

    Dim stXMLStream As New ADODB.Stream

    stXMLStream.Open

    With cn

    .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=ZAPHOD;User ID=sa;Password=ZZZZZZ;Initial Catalog=TEST"

    .CursorLocation = adUseClient

    .Open

    End With

    stXMLStream.Open

    With cmd

    .ActiveConnection = cn

    .CommandType = adCmdStoredProc

    .CommandText = "THMCMSJEXML"

    .Properties("Output Stream") = stXMLStream

    .Execute , , adExecuteStream

    End With

    stXMLStream.SaveToFile "C:\TESTREBUS.XML", adSaveCreateOverWrite

    stXMLStream.Close

    Set cmd = Nothing

    cn.Close

  • Thank you for your help...I really appreciate it.  Thanks for the tip about using ADO.  However in my situation, I think bcp is the best way for me to go.

    I tried using bcp but encountered some problems.  Here is what is in my stored procedure.

    DECLARE @query varchar (8000)

    SET @query = 'SELECT ''<ROOT> ''SELECT * FROM DM_INV.dbo.inv_lookup_ivg FOR XML AUTO,ELEMENTS SELECT ''<ROOT>'''      

    SELECT @bcp = 'bcp "' +  @query +  '" QUERYOUT "' + @varOutFile +'" -T -c'

    print @bcp

    EXEC @status = master.dbo.xp_cmdshell @bcp, no_output

    When I execute my stored procedure, all that is returned is the root tag.  If I remove the root tag, I receive results from the query but since there is no root tag, I am unable to view it in IE.  Is there a way to handle this?

     

  • I have a header and footer text file and use dos copy to glue them together after export. The header also maps the schema and template.

     

    Barbara

  • Thanks for the advice about using the text file and the dos copy.  The was able to created the xml document that I wanted to.  However, I am still experiencing a problem with viewing it in IE.  It seems that IE is still truncating the xml file.  Any other suggestions?

  • weird.. my reply yesterday never posted.

     

    Add the switch -r to your bcp command.

  • Thank you so much for all of your help!!!  The -r switch worked great.

  • DECLARE @query varchar (8000)

    declare @varoutfile varchar(20)

    declare @bcp varchar(500)

    declare @status int

    set @varoutfile = 'c:\test.xml'

    SET @query = 'SELECT ''<ROOT> ''SELECT name FROM sysobjects FOR XML AUTO,ELEMENTS SELECT ''<ROOT>'''     

    SELECT @bcp = 'bcp "' +  @query +  '" QUERYOUT "' + @varOutFile +'" -T -c -r'

    print @bcp

    EXEC @status = master.dbo.xp_cmdshell @bcp, no_output

     

    I tried the above and it didn't work. I also tried the vbscript above and it still does not give me formatted output. Any help wouldbe appreciated.

  • AUTO,ELEMENTS SELECT ''<ROOT>'''      should be changed to AUTO,ELEMENTS SELECT ''</ROOT>'''  . Iwould also recommend to insert union all between select statements

Viewing 10 posts - 1 through 9 (of 9 total)

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