Retrieve XML Data

Hi everyone,

I hope you can help!

I have the following query accesing an SQL Server 2000 Box

SELECT website.websiteID,
website.description,
website.URL,
website.DepartmentID
FROM website
WHERE website.canDisplay = 1

FOR XML AUTO, ELEMENTS

This comes back with

<website>
<websiteID>1</websiteID>
<description>yahoo</description>
<URL>http://www.yahoo.com</URL>
<DepartmentID>Department1</DepartmentID>
</website>

Now, thats the easy bit, any idea how I can save the Data coming back
into an XML file?

i.e. I want to save the data to c:\test.xml

Not sure what to use.

Thanks for the help!

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

Joyjit
Tue Dec 07 06:50:03 CST 2004

Hi,

Click on Query -> Results to File option in Sql Query Analyser before firing
the query.

Then give the appropriate filename when executing the query.

HTH
Joyjit

"nyousfi" <nyousfi@yahoo-dot-com.no-spam.invalid> wrote in message
news:41b58c46$1_2@Usenet.com...
> Hi everyone,
>
> I hope you can help!
>
> I have the following query accesing an SQL Server 2000 Box
>
> SELECT website.websiteID,
> website.description,
> website.URL,
> website.DepartmentID
> FROM website
> WHERE website.canDisplay = 1
>
> FOR XML AUTO, ELEMENTS
>
> This comes back with
>
> <website>
> <websiteID>1</websiteID>
> <description>yahoo</description>
> <URL>http://www.yahoo.com</URL>
> <DepartmentID>Department1</DepartmentID>
> </website>
>
> Now, thats the easy bit, any idea how I can save the Data coming back
> into an XML file?
>
> i.e. I want to save the data to c:\test.xml
>
> Not sure what to use.
>
> Thanks for the help!
>
> *-----------------------*
> Posted at:
> www.GroupSrv.com
> *-----------------------*

nyousfi
Tue Dec 07 07:56:29 CST 2004

Thanks but I want to do this programatically i.e. using ADO.NET

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

nyousfi
Tue Dec 07 11:59:15 CST 2004

Come on people, I'm sure someone has the answer.

The problem with searching the internet is that most of the
code/examples out there are old and are no longer supported.

Yours patiently

NY

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

anonymous
Tue Dec 07 13:10:15 CST 2004

DataSet ds = new DataSet();
SqlDataAdapter dap = new SqlDataAdapter("SELECT
websiteID, description, URL, DepartmentID FROM
website WHERE anDisplay = 1", connString);
dap.Fill(ds,"website");
ds.WriteXml(@"c:\test.xml");

>-----Original Message-----
>Hi everyone,
>
>I hope you can help!
>
>I have the following query accesing an SQL Server 2000 Box
>
>SELECT website.websiteID,
> website.description,
> website.URL,
> website.DepartmentID
> FROM website
> WHERE website.canDisplay = 1
>
>FOR XML AUTO, ELEMENTS
>
>This comes back with
>
><website>
> <websiteID>1</websiteID>
> <description>yahoo</description>
> <URL>http://www.yahoo.com</URL>
> <DepartmentID>Department1</DepartmentID>
></website>
>
>Now, thats the easy bit, any idea how I can save the Data
coming back
>into an XML file?
>
>i.e. I want to save the data to c:\test.xml
>
>Not sure what to use.
>
>Thanks for the help!
>
>*-----------------------*
> Posted at:
> www.GroupSrv.com
>*-----------------------*
>.
>

mweichert
Tue Dec 07 14:00:06 CST 2004

Hi,

Create a SqlCommand object with your select command.

so...


// Create query
string query = "SELECT website.websiteID, website.description,
website.URL, website.DepartmentID FROM website WHERE
website.canDisplay = 1 FOR XML AUTO, ELEMENTS";

// Create command
SqlCommand cmd = new SqlCommand(query, myConnectionObject);

// Create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);

// Create dataset
DataSet ds = new DataSet();

// Fill the dataset
da.Fill(ds, "Website");

// Write the xml
ds.WriteXml("C:\test.xml");


Hope that helps. :)
> nyousfiwrote:
Hi everyone,
>
> I hope you can help!
>
> I have the following query accesing an SQL Server 2000 Box
>
> SELECT website.websiteID,
> website.description,
> website.URL,
> website.DepartmentID
> FROM website
> WHERE website.canDisplay = 1
>
> FOR XML AUTO, ELEMENTS
>
> This comes back with
>
> <website>
> <websiteID>1</websiteID>
> <description>yahoo</description>
> <URL>http://www.yahoo.com</URL>
> <DepartmentID>Department1</DepartmentID>
> </website>
>
> Now, thats the easy bit, any idea how I can save the Data coming
back into an XML file?
>
> i.e. I want to save the data to c:\test.xml
>
> Not sure what to use.
>
> Thanks for the help![/code]

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

Joyjit
Wed Dec 08 01:23:30 CST 2004

Hi,

sorry for getting it late. But you got with it as someone else have already
answered.

Happy coding
Joyjit

"nyousfi" <nyousfi@yahoo-dot-com.no-spam.invalid> wrote in message
news:41b5ef73$1_1@Usenet.com...
> Come on people, I'm sure someone has the answer.
>
> The problem with searching the internet is that most of the
> code/examples out there are old and are no longer supported.
>
> Yours patiently
>
> NY
>
> *-----------------------*
> Posted at:
> www.GroupSrv.com
> *-----------------------*

Sahil
Thu Dec 09 01:42:24 CST 2004

Multiple ways to do this - another is ExecuteXmlQuery and use the XmlReader
you get in return. Or obviously fill a dataset, and write that out to Xml.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


"nyousfi" <nyousfi@yahoo-dot-com.no-spam.invalid> wrote in message
news:41b58c46$1_2@Usenet.com...
> Hi everyone,
>
> I hope you can help!
>
> I have the following query accesing an SQL Server 2000 Box
>
> SELECT website.websiteID,
> website.description,
> website.URL,
> website.DepartmentID
> FROM website
> WHERE website.canDisplay = 1
>
> FOR XML AUTO, ELEMENTS
>
> This comes back with
>
> <website>
> <websiteID>1</websiteID>
> <description>yahoo</description>
> <URL>http://www.yahoo.com</URL>
> <DepartmentID>Department1</DepartmentID>
> </website>
>
> Now, thats the easy bit, any idea how I can save the Data coming back
> into an XML file?
>
> i.e. I want to save the data to c:\test.xml
>
> Not sure what to use.
>
> Thanks for the help!
>
> *-----------------------*
> Posted at:
> www.GroupSrv.com
> *-----------------------*

nyousfi
Mon Dec 13 07:15:01 CST 2004

Here's my query again

SELECT website.websiteID, website.description, website.URL,
website.DepartmentID FROM website WHERE website.canDisplay = 1 FOR
XML AUTO, ELEMENTS

As most of you will know, this will not go into a DataSet, in order to
get it into a DataSet I would have to remove the 'FOR XML AUTO,
ELEMENTS' bit of my code. However, if I do that then the Dataset
will format the XML the way it wants to and not the way I want it

This is how I want my xml but datasets are saved in a different
format, too complicated to convert using an XSL stylesheet.

<website>
<websiteID>1</websiteID>
<description>yahoo</description>
<URL>http://www.yahoo.com</URL>
<DepartmentID>Department1</DepartmentID>
</website>

I appreciate all the help I'm getting and would love to get a solution
to this really annoying issue.

Thanks

NY

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

Sahil
Mon Dec 13 08:55:50 CST 2004

NY,

What you need is instead of a sqldataadapter, you need a SqlDataReader
instead. SqlDataReader has a ExecuteXmlQuery that gives you an XmlReader
which will respect the XML structure you are trying to extract out of the
database.

There are other solutions too, but this is the most straightforward, any
reason why this didn't work for you? Incidentally, Chapter #12 in my book
deals completely with "How to extract XML out of a Sql server database".

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik





"nyousfi" <nyousfi@yahoo-dot-com.no-spam.invalid> wrote in message
news:41bd95d5$1_2@Usenet.com...
> Here's my query again
>
> SELECT website.websiteID, website.description, website.URL,
> website.DepartmentID FROM website WHERE website.canDisplay = 1 FOR
> XML AUTO, ELEMENTS
>
> As most of you will know, this will not go into a DataSet, in order to
> get it into a DataSet I would have to remove the 'FOR XML AUTO,
> ELEMENTS' bit of my code. However, if I do that then the Dataset
> will format the XML the way it wants to and not the way I want it
>
> This is how I want my xml but datasets are saved in a different
> format, too complicated to convert using an XSL stylesheet.
>
> <website>
> <websiteID>1</websiteID>
> <description>yahoo</description>
> <URL>http://www.yahoo.com</URL>
> <DepartmentID>Department1</DepartmentID>
> </website>
>
> I appreciate all the help I'm getting and would love to get a solution
> to this really annoying issue.
>
> Thanks
>
> NY
>
> *-----------------------*
> Posted at:
> www.GroupSrv.com
> *-----------------------*