[thelist] Stored Proc to XML to Text File?

[Casey Crookston]

Anthony,

I am not familiar with SqlXmlCommand. I'm not getting VS to recognize it. 
(I'm in .NET 2.0.)  From reading up on it, looks mighty handy!

??

Thanks,

Casey


----- Original Message ----- 
From: "Anthony Baratta" <anthony at baratta.com>
To: <thelist at lists.evolt.org>
Sent: Monday, March 17, 2008 11:00 PM
Subject: Re: [thelist] Stored Proc to XML to Text File?


> 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;
>
>             try
>             {
>                 SqlXmlCommand cmd = new SqlXmlCommand(sConnectString);
>                 cmd.RootTag = <rootNodeName>;
>                 cmd.CommandType = SqlXmlCommandType.Sql;
>                 cmd.CommandText = sProcName
> + <any parameters the SP needs>;
>
>                 xr = cmd.ExecuteXmlReader();
>                 xd.Load(xr);
>             }
>             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.