XML string to SqlDataReader for SQL Server 2005 CLR Stored Procedure

microsoft.public.dotnet.framework.adonet

30 Mar 2006 9:33 PM

ehausig

I need some help getting an xml string into a SqlDataReader and I am
stuck. (I admit I'm no guru at this stuff.) I can easily get it into
a DataSet object, but the SqlContext.Pipe.Send() method needs a
SqlDataReader object as a parameter. (I don't want to send it as an
XML string.) Here is my method so far...any clues and code samples
would be greatly appreciated...I'm a visual learner. =)

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetAPList2()
    {
        // allow https access
        System.Net.ServicePointManager.CertificatePolicy = new
AirWave.TrustAllCertificatePolicy();

        string webURL =
"https://webservice.somesite.com/ap_list.xml?id=47";

        CredentialCache sec = new CredentialCache();
        sec.Add(new Uri(webURL), "Basic", new
NetworkCredential("*****", "*****"));

        WebClient client = new WebClient();
        client.Credentials = sec;

        string pageHTML =
Encoding.UTF8.GetString(client.DownloadData(webURL));

        Byte[] pageData = client.DownloadData(webURL);
        string pageHtml = Encoding.ASCII.GetString(pageData);

        DataSet xmlDS = new DataSet();
        XmlTextReader rdr = new XmlTextReader(pageHTML,
XmlNodeType.Document, null);
        xmlDS.ReadXml(rdr);

        SqlDataReader dr = /* STUCK HERE!???? */

        SqlContext.Pipe.Send(dr);

    }