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 =newXmlDocument();
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 =newXmlDocument();
xdoc.Load(GetXmlFile(pnrID).ToString());

publicstring GetXmlFile(int pnrID){

SqlConnection cnn =null;
SqlCommand cmd =null;

string XML ="";

try{

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

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

cmd =newSqlCommand(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 Files\Microsoft Visual Studio 8\Common7\IDE\System.Data.SqlTypes.SqlXml'.

Any idea how i can achive this?

If you want to load the XmlDocument from a string, then use the LoadXml method (i.e. xdoc.LoadXml(your_xml_string_here)).|||

Hi Vito, I did try LoadXml but it did not work.

The trick was rdr.GetSqlXml(0).CreateReader()); instead of rdr.GetSqlXml(0).ToString());

Below is my solution, if anyone have a better way of achiving this please let me know!

string selectQry ="SELECT [XML] FROM [TEMP_PNR] WHERE [PK_PnrID] = @.pnrID";
cmd =newSqlCommand(selectQry, cnn);
cmd.Parameters.AddWithValue("@.pnrID", pnrID);

XmlDocument xdoc =newXmlDocument();
SqlDataReader rdr = cmd.ExecuteReader();

if (rdr.Read())
xdoc.Load(rdr.GetSqlXml(0).CreateReader());

|||

I found instructions from Wrox bookProfessional ASP.NET 2.0 XMLISBN:0764596772 how to load XML data from SQL Server 2005 into aXmlDocument

Instead of
SqlDataReader rdr = cmd.ExecuteReader();

Do
XmlReader rdr = cmd.ExecuteXmlReader();

Instead of
if (rdr.Read())
xdoc.Load(rdr.GetSqlXml(0).CreateReader());

Do
if (rdr.Read())
xdoc.Load(rdr);

Hope this helps any other developers out there!

|||

This is the working code if anyone needs a template.

XmlDocument xdoc =newXmlDocument();
SqlConnection cnn =null;
SqlCommand cmd =null;

try
{
cnn =newSqlConnection();
cnn.ConnectionString ="xxxxxxxxxxxxxxxxx";
cnn.Open(); string selectQry ="SELECT [Xml] FROM [Table1] WHERE [PK_ID] = @.ID";
cmd =newSqlCommand(selectQry, cnn);
cmd.Parameters.AddWithValue("@.ID", ID); XmlReader reader = cmd.ExecuteXmlReader(); if (reader.Read())
xdoc.Load(reader);
}catch (Exception ex)
{
throw ex;
}finally
{
cmd.Dispose();
cnn.Close();
}