• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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.
0
xmalcolm
Asked:
xmalcolm
  • 4
1 Solution
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now