CODEWITHSUNDEEP
c#sqlxmlasp-classic
megha
megha

Reputation: 11

Export database to xml file in asp.net c#

On exporting all tables from a sql database to xml the table name nodes are named table,table1,table2 etc..How to get the table name exactly as in database on exporting to xml file?

Upvotes: 0

Views: 2788

Answers (3)

Krishna Kumar N
Krishna Kumar N

Reputation: 135

welcome to Stackoverflow.

I was also facing the same issue in one of my projects. As an alternative, I wrote the query to retrieve an XML in my column and write the data of retrieved from this column into my file.

The query example is as follows;

SELECT (
  SELECT TOP 1 NULL as N,
    (SELECT Employee.EmployeeId as Id, Employee.FirstName as Name,
      (SELECT TOP 1 NULL as N,
        (SELECT Customer.CustomerID as Id, CustomerName as Name
         FROM EmployeesCustomers INNER JOIN Customers Customer
         ON EmployeesCustomers.CustomerID = Customer.CustomerID
         WHERE EmployeesCustomers.EmployeeId = Employee.EmployeeId
         ORDER BY Customer.CustomerID
         FOR XML AUTO, TYPE)
       FROM Customers
       FOR XML AUTO, TYPE)
     FROM EmployeesCustomers INNER JOIN Employees Employee
     ON EmployeesCustomers.EmployeeID = Employee.EmployeeID
     GROUP BY Employee.EmployeeId, Employee.FirstName
     ORDER BY Employee.EmployeeId
     FOR XML AUTO, TYPE)
  FROM Employees
  FOR XML AUTO, ROOT('Company')
) AS COL_XML

The complete details is available in the following link (even though the link is for SSIS, the concept works in all cases) Click here

If you have a very complex query and cannot get the output as need, you can go ahead with the approach suggested by Siva Kumar Siddam to have the names of tables provided to your dataset.

Hope this helps

Upvotes: 0

pst
pst

Reputation: 59

if you using asp you need xdocument,xelement,xdocumenttype,xattribute,XDeclaration 

 XDocument XMLDocument=new XDocument();
    XDeclaration Declaration=new XDeclaration("1.0", "utf-8", "yes");
    XDocumentType doctype = new XDocumentType("filename", "", "",null);
    XMLDocument.AddFirst(doctype);
    XMLDocument.Declaration=Declaration;

Upvotes: 0

Siva Kumar Siddam
Siva Kumar Siddam

Reputation: 154

Not sure how exported, if you exported with DataSet, you must not be having table names for each table in DataSet, make sure that tables in your dataset has names.

        DataSet ds = new DataSet();
        foreach (DataTable d in ds.Tables)
        {
            d.TableName = "TableName";
        }
        ds.WriteXml("filepath");
        //Or
        DataTable dt = new DataTable("TableName");
        dt.WriteXml("filepath");

Upvotes: 1

Related Questions