How can I fetch data from my local database and sterilize to XML format?-CodePudding

I'm new to ASP.NET Core. I want to know is it possible to fetch and sterilize data from MSSQL database to an XML file using c# or any other method? I am using Microsoft SQL Server 15.0.2080.9 and .net 5.0 in visual studio

Sql Database Diagram

CodePudding user response：

Here is a simple demo about how to dowload xml file from database:

Model:

public class Test
{
    public int Id{ get; set; }
    public string Title { get; set; }
    public string Name{ get; set; }
    public List<Item> Items{ get; set; }
}
public class Item
{
    public int Id{ get; set; }
    public string Name { get; set; }
}

Controller:

public IActionResult Index()
{
    XmlSerializer serializer = new XmlSerializer(typeof(Test));
    //get the data you want from the database
    Test po = _context.Test.Include(a=>a.Items).ToList();
        
    var stream = new MemoryStream();
    //serialize to xml
    serializer.Serialize(stream, po);

    //download the xml file
    return File(stream.ToArray(), "application/xml", "test.xml");
}

CodePudding user response：

Please be more specific if you're using MSSQL and MS SQL server then you can use it's export feature for data. I think here you're asking for your schema structure to convert it to XML is that right?

Sample code for Exporting data from table.

SELECT SalesRegion.SalesTerritoryRegion as Region,
    SUM(InternateSales.SalesAmount)  as SalesAmount
    FROM dbo.FactInternetSales  InternateSales

LEFT JOIN dbo.DimSalesTerritory SalesRegion
ON InternateSales.SalesTerritoryKey = SalesRegion.SalesTerritoryKey

GROUP BY SalesRegion.SalesTerritoryRegion

FOR XML AUTO,TYPE, ELEMENTS ,ROOT('RegionSales')

CodePudding user response：

If you want to export the whole SQL Server database to the XML file, you can first use the SQL Server Management Studio to export your SQL database to CSV, and then convert CSV to XML using an online converter. You can also choose to output SQL Server database to Excel and then convert Excel (XLS) to XML.

This link will help you to do step by step.

Thank you .