Retrieve XML Data

  • Thread starter Thread starter nyousfi
  • Start date Start date
N

nyousfi

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
*-----------------------*
 
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
 
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
*-----------------------*
 
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");
 
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]
Click to expand...

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

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

Happy coding
Joyjit
 
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
*-----------------------*
 
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
 
Back
Top