How Do I Load SQL Server XML Datatype Into XmlDocument In C#

Hi there,

How do i load a XML document saved in SQL server as a XML data type into

XmlDocument xdoc = new XmlDocument();
xdoc.Load(// INTO HERE );

I can load a xml file saved to disk but haven't figured out how to retrive from a recordset. I have tried the following:

 

XmlDocument xdoc = new XmlDocument();
xdoc.Load(GetXmlFile(pnrID).ToString());

 

public string GetXmlFile(int pnrID){

SqlConnection cnn = null;
SqlCommand cmd = null;

string XML = "";

  try {

            cnn = new SqlConnection();
            cnn.ConnectionString = "Server=;Initial Catalog=;UID=;PWD=;";
            cnn.Open();

            string selectQry = "SELECT [XML] FROM [TEMP_PNR] WHERE PnrID = @PnrID";

            cmd = new SqlCommand(selectQry, cnn);

            cmd.Parameters.AddWithValue("@pnrID", pnrID);

            SqlDataReader rdr = cmd.ExecuteReader();

            if (rdr.Read())
            XML = rdr.GetSqlXml(0).ToString();

        }

catch (Exception ex)
           {
            throw ex; 
           }

   finally {
            cmd.Dispose();
            cnn.Close();
           }

return XML;

}

But this genereates the following error: Could not find file 'C:Program FilesMicrosoft Visual Studio 8Common7IDESystem.Data.SqlTypes.SqlXml'.

Any idea how i can achive this?