February 7, 2005 at 11:57 am
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
Had the same problem...
export the data in a data stream - ado or bcp work.
February 8, 2005 at 10:42 am
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
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
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
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
weird.. my reply yesterday never posted.
Add the switch -r to your bcp command.
February 10, 2005 at 11:38 am
Thank you so much for all of your help!!! The -r switch worked great.
February 10, 2005 at 2:41 pm
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
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