XML metadata?

  • Thread starter Thread starter Girish
  • Start date Start date
G

Girish

Is there a way I can expose the metadata from sql server of a database in a
well defined XML format that sql server can supply to me? I dont want to
write xml schemas myself and fetch the metadata and adapt the information to
my schema - id rather just get a xml stream and I can adapt my application
to the schema it generates.

thanks,
Girish
 
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."
 
Back
Top