extracting results of XML stored proc to file

Hi all, I need a bit of guidance!
I have created a few queries using FOR XML AUTO, but am unsure how to extract them from the results into an XML file
out side of the sql server. is there a way I can make the stored procedure push the result to an xml file on my server? Or do
I have to use ado to extract the data from the server. I would rather push than pull!
thanks in advance!
Mike
There's no easy way to do it using T-SQL (I guess you could write an
extended Stored Proc or something, but it'd be a lot of effort.)
Your best bet is to install SQLXML 3.0
(http://www.microsoft.com/downloads/d...33a9-cf10-4e22
-8004-477098a407ac&displaylang=en) and use a script like the following
example to generate the file. You could then wrap this script up in a SQL
Agent job and execute it from within SQL Server.
Example script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=localhost;DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Specify the root tag
cmd.Properties("xml root") = "QueryResults"
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "MyStoredProc"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
' Save the file
xmlDoc.Save("C:\Results.xml")
MsgBox("File saved")
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike UK" <anonymous@.discussions.microsoft.com> wrote in message
news:6DA91318-4FF8-4DF7-B27B-0CDA3A876389@.microsoft.com...
> Hi all, I need a bit of guidance!
> I have created a few queries using FOR XML AUTO, but am unsure how to
extract them from the results into an XML file
> out side of the sql server. is there a way I can make the stored procedure
push the result to an xml file on my server? Or do
> I have to use ado to extract the data from the server. I would rather push
than pull!
> thanks in advance!
> Mike
|||Thanks Graeme, I will certainly give it a go! I must admit I m a little suprised we cant export from a stored proc,
especiall as we can get the results of a FOR XML query into the results pane.
Thanks again for the script!
Mike.
|||Hi Mike,
You can export from an SP! - We're doing it via BCP (bulk copy) and XML templates. It is a bit tedious to write but it has been working very well in our production environment for the past 3 months.
We have one stored proc that contains our FOR XML statement. For the purposes of discussion, I'll call it 'record_return' (in our case, we're using FOR XML EXPLICIT but it would not make a difference using AUTO) and another stored procedure 'launch_record
_return' that calls 'record_return'.
Within launch_record_return we build a dynamic SQL statement and execute the call to BCP. In our case, we are dynamically building the full path and file name for the exported XML.
Example (from within launch_record_return):
-- create full file path for our output XML document
SET @.xmlname = '\'+@.ord+'.xml' (in this case, @.ord is the name of the file which we're generating dynamically)
SET @.finalname = '\xx'+@.ord+'.xml'
SET @.fname=@.fullDirPath+@.xmlname
SET @.fullPathAndFinalName = @.fullDirPath+@.finalname
SET @.fname=@.fullDirPath+@.xmlname
SET @.bcpCommand = 'bcp "EXEC DB_NAME..record_return ' + "'" + @.ord + "'" + '"' +" queryout "
SET @.bcpCommand = @.bcpCommand + @.fname + ' -c -t -r'
SET @.appendCommand = 'copy/b c:\yourdirectory\header.txt+'+@.fname+'+c:\yourdire ctory\footer.txt '+ @.fullPathAndFinalName
-- execute commands
EXEC master..xp_cmdshell @.bcpCommand
EXEC master..xp_cmdshell @.appendCommand
-- delete temp case file
SET @.appendCommand = ' del ' + @.fname
EXEC master..xp_cmdshell @.appendCommand
The header.txt and footer.txt files contain the head of the document because you'll find that BCP does not wrap the XML in a root element.
This code would of course need to be revised to meet your specific requirements but should provide you with some insight on how to go about generating an actual XML file from an SP. We have also setup a DTS job that runs the 'launch' procedure because the
re is a bug (http://support.microsoft.com/default...;en-us;Q275583) that occurs when executing FOR XML statements via ODBC. We found that it was easy to get around this by creating a DTS job and connecting via OLE.
Hope this helps.
Have a great weekend.
Any questions, feel free to email me.
Dee
Denise E. White
IT Developer
Legal Marketing Services
Cheshire, England
|||Hi Denise,
I thank you for this great post. One thing I wanted to note - a way to get your XML Explicit sproc to have a root element without appending text files: start your sproc with a record that from your highest level table that you know occurs only once, per
haps a transaction identifier value, or perhaps a string value like your company name and wrap it as below
SELECT
1 AS Tag,
NULL AS Parent,
TR.TransSetCntrlNmbr AS [ROOT!1!Filler!Hide],
....
|||Thanks.This is a great post.can any one post the complete
code of the procedure?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks JT...I have been using this technique however, I still include a header and footer file beacause I cannot find a way using this method to have the root element contain a reference to my XSD schema.
Any thoughts?
Denise