Post reply

XML results are seperated into lines of 2033 characters with ODBC

  • February 7, 2005 at 11:57 am

    #87392

    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!

  • February 8, 2005 at 4:00 am

    #541090

    Had the same problem...

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

  • February 8, 2005 at 10:42 am

    #541182

    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

    James Horsley
    Workflow Consulting Limited

  • February 8, 2005 at 11:56 am

    #541213

    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?

     

  • February 8, 2005 at 1:27 pm

    #541251

    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

  • February 9, 2005 at 10:59 am

    #541419

    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?

  • February 10, 2005 at 11:06 am

    #541634

    weird.. my reply yesterday never posted.

     

    Add the switch -r to your bcp command.

  • February 10, 2005 at 11:38 am

    #541636

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

  • February 10, 2005 at 2:41 pm

    #541682

    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.

  • February 11, 2005 at 5:17 am

    #541768

    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