sql query again

sql query again

Post by kevi » Wed, 06 Mar 2002 16:24:22



is it possible to save an xml file from a stored
procedure that uses the FOR XML clause in the same way as
objrs.save with a disconnected recordset
 
 
 

sql query again

Post by MPE Solution » Wed, 06 Mar 2002 22:40:51


You could always just use FSO to save the XML you get back to a file. There
are other more elegant solutions I am assume, but that is a sure way to do
it. Also, if you are interested in a 3rd party solution you might take a
look at http://www.xdac.net, specifically its file saving capabilities
directly after calling SQL


Quote:> is it possible to save an xml file from a stored
> procedure that uses the FOR XML clause in the same way as
> objrs.save with a disconnected recordset


 
 
 

sql query again

Post by Avner Aharo » Thu, 07 Mar 2002 01:59:38


Hi Kevin,

You can load the results to MSXML2.DOMDocument and save it.
For example you can call your stored proc from template file (using
SQLXML3) in the follwing way
Dim oTestStream As New ADODB.Stream
Dim oTestConnection As New ADODB.Connection
Dim oTestCommand As New ADODB.Command
Dim xmlDoc As New MSXML2.DOMDocument40
oTestConnection.Open "provider=SQLXMLOLEDB.3.0;data provider=SQLOLEDB;data
source=(local);initial catalog=Northwind;user
id=UserName;password=UserPassword;"
oTestCommand.ActiveConnection = oTestConnection
oTestCommand.CommandText = _
        "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql' >" & _
       " <sql:query> " & _
        "   EXEC MYStoredProc " & _
        "   </sql:query> " & _
        " </ROOT> "
oTestStream.Open
' You need the dialect if you are executing a template.
oTestCommand.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
oTestCommand.Properties("Output Stream").Value = oTestStream
oTestCommand.Properties("Base Path").Value = "c:\Schemas\SQLXMLWR2\New
Folder\ExecuteTemplateWithXSL\"
oTestCommand.Execute , , adExecuteStream

oTestStream.Position = 0
oTestStream.Charset = "utf-8"
xmlDoc.loadXML oTestStream.ReadText(adReadAll)
xmlDoc.save "myFile.xml"

If you save your template in a Vdir you can simply load the document in the
following way
xmlDoc.load "http://VDir/VName/FileName"
'Save it
xmlDoc.save "MyFile"

Take a look at SQLXML3 additional examples in the help file

HTH
Avner

This posting is provided "AS IS" with no warranties, and
confers no rights.