April 11, 2002 at 8:31 am
In the normal fashion (return result of a Select ... For XML Explicit to a file in SQL Query Analyzer) of creating a xml file from a SQL query, the single row returned is always cut short. This invalidates the XML File. I increased the max chars in a column parameter (Tool>Options>Results in Query analyzer) to 8192. Resultset string still too long.
I have succeeded in returning the full resultset in XML format via the XML query for SQL facility and can view this in IE just fine. I need to automate the process to create an XML File in order to transfer this data.
Any ideas?
April 11, 2002 at 9:10 am
ADO stream object is the best way. I dont have sample code handy but you can probably find some either here or MSDN.
Andy
April 11, 2002 at 11:48 am
Have to write some code. Could use isql/osql and pipe to a file, but even DTS doesn't handle this well. ADO is the best method.
Steve Jones
April 11, 2002 at 2:26 pm
Will investigate ADO Stream.
Steve, I tried ISQL :> Piping to an output file but this had same limitation as the autocreation of the XML file from within query analyzer ; seemingly the column width.
Thks so far.
April 14, 2002 at 12:17 pm
check out my article ASP - Reaping the benefits of SQL 2000's FOR XML clause.
http://www.sqlservercentral.com/columnists/lplatt/article4_1.asp
April 15, 2002 at 9:48 am
Hi there
My immediate goal has been reached in that I created a stored proc which ran a FOR XML EXPLICIT statement. A colleague wrote a VB Script which essentially ran the stored proc and persisted the resultset to an XML formatted file. This script has been implemented in a DTS activeX task as part of an automation process.
The content of the DTS task follows:
'**********************************************************************
' Visual Basic ActiveX Script
' Author: Kristian Reynolds
' Delos Technologies
' http://www.delostechnology.com
'************************************************************************
'*********
'********
Function Main()
'populate global variables
SQL_Temp = "EXEC CREATE_XML"
Local_Dest = "c:\AAAtestfile.xml"
Dim conn
Dim cmd
Dim strmOut
Dim strmIn
Dim xmlstring
Dim fso
Dim ts
Dim f
Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set strmOut = CreateObject("ADODB.Stream")
Set strmIn = CreateObject("ADODB.Stream")
' set up connection
conn.Open "provider=sqloledb; data source=SHOPRITE_CMX; initial catalog=<DatabaseName;Trusted_connection=Yes"
Set cmd.ActiveConnection = conn
' XML dialect
cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
' build input stream using XML and template
strmIn.Open
strmIn.WriteText "<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>" & SQL_Temp & "</sql:query></root>", 0
strmIn.Position = 0
Set cmd.CommandStream = strmIn
' set up output stream for resulting XML
strmOut.Open
cmd.Properties("Output Stream").Value = strmOut
cmd.Properties("Output Encoding").Value = "UTF-8"
cmd.Execute , ,1024 'adExecuteStream
strmOut.Position = 0
' read stream contents into a string
While Not strmOut.EOS
xmlstring = xmlstring & strmOut.ReadText(1024)
Wend
' create output file and write xml string to it
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CreateTextFile Local_Dest ' Create a file.
Set f = fso.GetFile(Local_Dest)
Set ts = f.OpenAsTextStream(2, -1) 'ForWriting, TristateUseDefault
Call ts.write(xmlstring)
' close and deallocate objects
ts.close
strmIn.Close
strmOut.Close
conn.Close
Set ts = Nothing
Set f = Nothing
Set fso = Nothing
Set strmIn = Nothing
Set strmOut = Nothing
Set conn = Nothing
Set cmd = Nothing
SQL_Temp = ""
Local_Dest = ""
Main = DTSTaskExecResult_Success
End Function
June 21, 2017 at 1:13 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply