Save XML from SQL

[Guest]

I have a page that has javascript that relies on being able to read an XML
file. This XML file contains data that will be used to place markers on a
Google Map object.
The data in this file is held in a SQL 2005 database.
What Im unsure about is the most efficient way to get the data from my SQL
server and then saved as an XML file on the webserver.
I have create a stored procedure that returns the required data as xml

SELECT
addr_int_ID ID,
addr_txt_Lat Lat,
addr_txt_Lng Lng,
addr_int_Type "Type",
addr_txt_Tooltip Tooltip,
addr_txt_BubbleText BubbleText
from
GMarker
for xml auto


How do I dump this into a file in my web directory?

 

[John Saunders [MVP]]

I have a page that has javascript that relies on being able to read an XML
file. This XML file contains data that will be used to place markers on a
Google Map object.
The data in this file is held in a SQL 2005 database.
What Im unsure about is the most efficient way to get the data from my SQL
server and then saved as an XML file on the webserver.
I have create a stored procedure that returns the required data as xml

SELECT
addr_int_ID ID,
addr_txt_Lat Lat,
addr_txt_Lng Lng,
addr_int_Type "Type",
addr_txt_Tooltip Tooltip,
addr_txt_BubbleText BubbleText
from
GMarker
for xml auto


How do I dump this into a file in my web directory?

I have a page that has javascript that relies on being able to read an XML
file. This XML file contains data that will be used to place markers on a
Google Map object.
The data in this file is held in a SQL 2005 database.
What Im unsure about is the most efficient way to get the data from my SQL
server and then saved as an XML file on the webserver.
I have create a stored procedure that returns the required data as xml

SELECT
addr_int_ID ID,
addr_txt_Lat Lat,
addr_txt_Lng Lng,
addr_int_Type "Type",
addr_txt_Tooltip Tooltip,
addr_txt_BubbleText BubbleText
from
GMarker
for xml auto

Don't dump it into a file. Read the data directly from SQL Server.

Try this in the AdventureWorks database:


SELECT (
SELECT [dbo].[Contacts].[ContactID],
[dbo].[Contacts].[FirstName],
[dbo].[Contacts].[LastName],
[dbo].[Contacts].[City],
[dbo].[Contacts].[State],
[dbo].[Contacts].[Zip] FROM Contacts
FOR XML AUTO,TYPE,ROOT('YourRootElement')
) AS XML

This will produce:

<YourRootElement>
<Contacts ContactID="19" FirstName="Office" LastName="Home"
City="Jacksonville " State="FL" Zip="32276-1911" />
<Contacts ContactID="20" FirstName="Office" LastName="Murphy "
City="Jacksonville " State="FL" Zip="32214-1823" />
<Contacts ContactID="21" FirstName="Andrei " LastName="Ranga " City="Jax "
State="FL" Zip="32276 " />
....
</YourRootElement>

This is returned as a single column named "XML". It will be of the new SQL
Server datatype "xml", and you can do many things with it, including sending
it the client's browser.

 

[Guest]

(be> Don't dump it into a file. Read the data directly from SQL Server.

This is returned as a single column named "XML". It will be of the new SQL
Server datatype "xml", and you can do many things with it, including sending
it the client's browser.

But this would require me to modify the javascript on the page which is
undesirable. There are a number of pages that would use this xml file and I
do not want to go down the root of modifying these pages (partly because my
javascript knowledge is very basic)

 

[John Saunders [MVP]]

(be> Don't dump it into a file. Read the data directly from SQL Server.


But this would require me to modify the javascript on the page which is
undesirable. There are a number of pages that would use this xml file and
I
do not want to go down the root of modifying these pages (partly because
my
javascript knowledge is very basic)

Oh, so you load the data via URL?

 

[Guest]

Oh, so you load the data via URL?

This is the Javascript on the page that opens the xml doc

// Read the data from example.xml
var request = GXmlHttp.create();
request.open("GET", "example.xml", true);
request.onreadystatechange = function() {
if (request.readyState == 4) {
var xmlDoc = GXml.parse(request.responseText);
// obtain the array of markers and loop through it
var markers = xmlDoc.documentElement.getElementsByTagName("marker");

//MORE PROCCESSING OF XML

as I said previously, my javascript knowledge is quite basic. I know that
if I have an xml file in my web directory containing all of the data I need
to display on my GMap, then this and many other pages work perfectly well.

So, for this particular project, what I would like to know is how to get the
data from SQL as XML and store it as a file on the web server.

Out of interest, how would I use your suggestion of using the new xml data
type in my particular example?

 

[John Saunders [MVP]]

This is the Javascript on the page that opens the xml doc

// Read the data from example.xml
var request = GXmlHttp.create();
request.open("GET", "example.xml", true);
request.onreadystatechange = function() {
if (request.readyState == 4) {
var xmlDoc = GXml.parse(request.responseText);
// obtain the array of markers and loop through it
var markers =
xmlDoc.documentElement.getElementsByTagName("marker");

//MORE PROCCESSING OF XML

as I said previously, my javascript knowledge is quite basic. I know that
if I have an xml file in my web directory containing all of the data I
need
to display on my GMap, then this and many other pages work perfectly well.

So, for this particular project, what I would like to know is how to get
the
data from SQL as XML and store it as a file on the web server.

Out of interest, how would I use your suggestion of using the new xml data
type in my particular example?

You would do it by creating a page to return your XML. Instead of
"example.xml", you'd use "example.aspx". This page would set
Response.ContentType to "text/xml", and would send the XML directly from SQL
Server using Response.Write. It would be something like this:

using System;
using System.Data.SqlClient;
using System.Web.UI;
using System.Xml;

public partial class Example : Page
{
private const string CONNECTION_STRING =
"Data Source=localhost;Initial Catalog=AdventureWorks;Integrated
Security=True";
private const string XML_COMMAND =
@"
SELECT (
SELECT [dbo].[Contacts].[ContactID],
[dbo].[Contacts].[FirstName],
[dbo].[Contacts].[LastName],
[dbo].[Contacts].[City],
[dbo].[Contacts].[State],
[dbo].[Contacts].[Zip] FROM Contacts
FOR XML AUTO,TYPE,ROOT('YourRootElement')
) AS XML
";

protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection connection = new
SqlConnection(CONNECTION_STRING))
{
connection.Open();
using (SqlCommand command = new SqlCommand(XML_COMMAND,
connection))
{
using (XmlReader reader = command.ExecuteXmlReader())
{
reader.Read();
Response.Write(reader.ReadOuterXml());
}
}
}
}
}

Of course, having an XmlReader means that you can do any XML processing you
might like on the result. You can run an XSLT transform, for instance.