[thelist] Stored Proc to XML to Text File?

Anthony Baratta anthony at baratta.com
Mon Mar 17 23:00:36 CDT 2008

You need to use the SqlXmlCommand to get the data returned as XML.

Something like this (code based on SQLXML 3.0):

             XmlDocument xd = new XmlDocument();
             XmlReader xr;

                 SqlXmlCommand cmd = new SqlXmlCommand(sConnectString);
                 cmd.RootTag = <rootNodeName>;
                 cmd.CommandType = SqlXmlCommandType.Sql;
                 cmd.CommandText = sProcName
			+ <any parameters the SP needs>;

                 xr = cmd.ExecuteXmlReader();
             catch (Exception exp)
                 DBError(exp, "execSPForXML: SqlXmlCommand /" 	
			+ "cmd.ExecuteXmlReader (" + sProcName + ")");
                 return xd;
             return xd;

Note you will need to prepend "Provider=SQLOLEDB;" to your connection 
string for the SQLXML 3.0 methods.

This example returns an XmlDocument, that you can then write to the hard 
drive or do with what you will.

Hope this gets you in the right direction.

Anthony Baratta

"A government that is big enough to
give you all you want is big enough
to take it all away."
--Barry Goldwater

More information about the thelist mailing list