Hi Girish,
First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to export all data in a SQL
server database to Xml. If there is any misunderstanding, please feel free
to let me know.
As far as I know, we cannot export everything from the database. Generally,
we export all the tables in that database. It is recommended to use
ADO.NET. We can create a typed DataSet, fill the typed DataSet with data in
the database tables. And then write data or schema to XML. Make sure to
drag all tables into the typed DataSet, and create relationship according
to the database. Here I've written a code snippet for you.
SqlDataAdapter sda1 = new SqlDataAdapter("SELECT * FROM Employees",
this.sqlConnection1);
SqlDataAdapter sda2 = new SqlDataAdapter("SELECT * FROM Customers",
this.sqlConnection1);
SqlDataAdapter sda3 = new SqlDataAdapter("SELECT * FROM Orders",
this.sqlConnection1);
Dataset1 ds = new Dataset1();
sda1.Fill(ds.Employees);
sda2.Fill(ds.Customers);
sda3.Fill(ds.Orders);
ds.WriteXml(@"c:\data.xml"); //writes data
ds.WriteXmlSchema(@"c:\schema.xml"); //writes schema
For more information about WriteXml and WriteXmlSchema method, please check
the following links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatasetclasswritexmltopic.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatasetclasswritexmltopic.asp
Also, we can export to Xml from the server directly using FOR XML clause
for SELECT statement. Here is an example.
SELECT Customers.CustomerID, ContactName, CompanyName,
Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND (Customers.CustomerID = N'ALFKI'
OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.CustomerID
FOR XML AUTO
Please check the following link for more information.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_o
penxml_759d.asp
HTH. If anything is unclear, please feel free to reply to the post.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
