XML query

Hi Guys, I have a query regarding XML.
I have an XML file which I am using to provide address data to my google map. I have manually added data to the xml file. But is it possible for the XML file to update itself automatically from SQL server database?

If so how?
Or is it possible for the XML to gather all data from a table in the database when I load my website, and I could have a query on the server side, which would allow specific addresses to be picked from the XML file?

Please do not hesitate to ask me any questions if you are unclear.
xmalcolmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajvjaCommented:
Hi,

  It is possible to load your xml file automatically from the database when the website loads.
I assume your website is designed using Asp.net and IIS
If so, try this link

http://www.c-sharpcorner.com/UploadFile/prabhashchandra/AspNET02162008031739AM/AspNET.aspx
0
xmalcolmAuthor Commented:
Yes it is - Thanks for the link I shall have a look at it.
0
xmalcolmAuthor Commented:
Ah yes this could work. I will let you know if it does. Thanks :)
0
xmalcolmAuthor Commented:
Hi Raj,

I am trying to get the code to work but I am having problems. Could it be as I have my database connected to using dataconnection instead of having it in app_data? Also as I mentioned earlier, I only want the data from the database to transferred to the xml file and not show on a page. Therefore I modified the code as shown in the link provided above.

Here is the code:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.Xml;
using System.IO;

public partial class Login : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new
            SqlConnection(ConfigurationManager.ConnectionStrings["Car_Share_XG"].ConnectionString);
        ConnectionXML();
    }
    public void ConnectionXML()

{
    SqlCommand command = new SqlCommand();
    command.CommandText = "SELECT * FROM tbl_Member";
    command.CommandType = CommandType.Text;
    command.Connection = con;
    SqlDataAdapter da = new SqlDataAdapter(command);
    DataSet ds = new DataSet();
    da.Fill(ds, "tbl_Member");

    // Get a FileStream object
    StreamWriter xmlDoc = new StreamWriter(Server.MapPath("~/Testdo.xml"), false);
    // Apply the WriteXml method to write an XML document
    ds.WriteXml(xmlDoc);
    xmlDoc.Close();
}
   
}


And here is the error message I am getting:

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0103: The name 'con' does not exist in the current context

Source Error:

Line 30:     command.CommandText = "SELECT * FROM tbl_Member";
Line 31:     command.CommandType = CommandType.Text;
Line 32:     command.Connection = con;
Line 33:     SqlDataAdapter da = new SqlDataAdapter(command);
Line 34:     DataSet ds = new DataSet();

What am I doing wrong? It cannot recognise the 'con' term.
0
xmalcolmAuthor Commented:
Solution attached
SqlConnection myConn = new SqlConnection();
        myConn.ConnectionString = "Data Source=XG7;Initial Catalog=Car_Share_XG;Integrated Security=True";
        myConn.Open();
        SqlCommand command = new SqlCommand();
        command.CommandText = "SELECT Member_Address FROM tbl_Member";
        command.CommandType = CommandType.Text;
        command.Connection = myConn;
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        da.Fill(ds, "tbl_Member");

        // Get a FileStream object
        StreamWriter xmlDoc = new StreamWriter(Server.MapPath("./out1.xml"), false);
        // WriteXml method to write an XML document
        ds.WriteXml(xmlDoc);
        xmlDoc.Close();
        myConn.Close();

        XmlDocument sourceDocument = new XmlDocument();
        sourceDocument.Load(Server.MapPath("./out1.xml"));

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.