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();
}
