How to make sql query result to xml file

Try this:

SELECT *
FROM dbo.YourStudentTable
FOR XML PATH('Student'), ROOT ('Students')

This should return exactly the XML you're looking for (assuming you have SQL Server 2005 or newer)

Read more about how to use FOR XML PATH and its capabilities on TechNet


SQL Server: just an addition to @marc_s answer - note that xml is case-sensitive, and resulting xml will look like

<Students>
   <Student>
      <ID>1</ID>
      <Name>a</Name>
      <Number>123</Number>
      <Email>a@a.com</Email>
  </Student>
</Students>

and if you'll try to retrieve id, you'll not find anything.

you may want to do something like this:

select
    ID as id,
    Name as name,
    Number as number,
    Email as email
from dbo.Table1
for xml path('Student'), root('Students')

=> sql fiddle example.

C#: you can use WriteXml method:

var ds = new DataSet("Students");
var dt = ds.Tables.Add("Student");
dt.Columns.Add("id", typeof(int));
dt.Columns.Add("name", typeof(string));
dt.Columns.Add("number", typeof(string));
dt.Columns.Add("email", typeof(string));

dt.Rows.Add(1, "a", "123", "a@a.com");
dt.Tables[0].Rows.Add(2, "b", "321", "b@b.com");
dt.Tables[0].Rows.Add(3, "c", "432", "c@c.com");
var stream = new StringWriter();
ds.WriteXml(stream);

or using Linq to XML:

 new XElement("Students", dt.AsEnumerable().Select(r =>
       new XElement("Student",
            new XElement("id", r["id"]),
            new XElement("name", r["name"]),
            new XElement("number", r["number"]),
            new XElement("email", r["email"])
       )));

1) Create class called student

[Serializable]
public class Student
{
  public int ID { get; set; }
  public string Name { get; set; }
  public int Number { get; set; }
  public string Email { get; set; }
}

2) Get data into List called StudentListfrom Database

3)Then open or create xml file and add values

using (XmlWriter writer = XmlWriter.Create("Student.xml"))
{
    writer.WriteStartDocument();
    writer.WriteStartElement("Students");

    foreach (Student student in StudentList)
    {
    writer.WriteStartElement("Student");

    writer.WriteElementString("id", student.ID.ToString());
    writer.WriteElementString("name", student.Name.ToString());
    writer.WriteElementString("number", student.Number.ToString());
    writer.WriteElementString("email", student.Email.ToString());

    writer.WriteEndElement();
    }

    writer.WriteEndElement();
    writer.WriteEndDocument();
}

You can use DataSet.GetXml() function for getting result in XML format file


Though the solution provided by marc is exactly what you need, you may want to have a deeper look at various options in the article Using the FOR XML Clause to Return Query Results as XML.


Comments

  1. Derrick

    • 2015/4/30

    5 Answers · 1) Create class called student [Serializable] public class Student { public int ID { get; set; } public string Name { get; set; } 

  2. Cody

    • 2016/9/20

    Once xp_cmdshel is enabled in the SQL Server. You can use the following command to export the data to an xml file. EXEC xp_cmdshell 'bcp "SELECT [Created], [Text] FROM [db304]. [dbo]. [SearchHistory] FOR XML PATH (''Record''), ROOT (''SearchHistory'')" queryout "C:\bcptest.xml" -T -c -t,'. Share.

  3. Dillon

    • 2015/1/6

    . The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.

  4. Leroy

    • 2019/11/10

    Export SQL Database to XML. Step 1. Open SQL Server Management Studio and connect to the database. Step 2. Go to "Object Explorer", find the server database you want to export in CSV. Right-click on it and choose "Tasks" > "Export Data" to export Step 3. Now, you need to click on the Data source

  5. Kohen

    • 2020/10/1

    . Be aware of the following before deciding to use bcp for your regular export requirements: bcp is a program external to SSMS. If you need to use this from within your scripts, you will need to enable xp_cmdshell.

  6. Kingston

    • 2019/5/11

    -- Read your query results into an XML variable DECLARE @xml AS XML = (SELECT * FROM YourTable FOR XML PATH) -- Cast the XML variable into a VARCHAR DECLARE @xmlChar AS VARCHAR(max) = CAST(@xml AS VARCHAR(max)) -- Escape the < and > characters SET @xmlChar = REPLACE(REPLACE(@xmlChar, '>', '^>'), '<', '^<') -- Create command text to echo to file DECLARE @command VARCHAR(8000) = 'echo ' + @xmlChar + ' > c:\test.txt' -- Execute the command EXEC xp_cmdshell @command

  7. Azariah

    • 2016/8/15

    FOR XML keyword is a provision that can be added with the SELECT query statement to prepare the XML document in the required forms in a 

  8. Malachi

    • 2015/11/8

    Make sure your xml variable is global, so that the SQL loop appends new elements to the XML. If the xml variable was not global, its value would be forgotten on each pass of the loop. Save your XML file to the file system. In Desktop Automation Open the XML file in a Browse Step. you can then loop through the XML.

  9. Sullivan

    • 2017/1/15

    SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR 

  10. Spencer

    • 2019/5/22

    SELECT T.ItemNumber AS [@ID], (SELECT T.ItemNumber, (SELECT sq.Price AS [@value], (SELECT sq.DateFrom, sq.DateTo FOR XML PATH(''),TYPE) FROM #tempXML sq WHERE sq.ItemNumber = T.ItemNumber FOR XML PATH('Price'),TYPE) FOR XML PATH(''),TYPE) FROM #tempXML T GROUP BY ItemNumber FOR XML PATH ('Item'); This results in:

  11. Vincent

    • 2019/1/8

    Solution: · Create an SQL query and save it to the file. · run following: sqlcmd -S -U sa -P sapassword -i inputquery_file_name -C65001 -o outputfile_name 

  12. Caputo

    • 2017/10/10

    DECLARE @Output XML; SET @Output = ( SELECT FOR XML ; ); DECLARE @ErrorMessage NVARCHAR(4000); SET @ErrorMessage = dbo.SaveXmlToFile(N'path/to/file.xml', @Output); A few easy steps to get the above SQLCLR function working (and pretty much any Assembly you create that needs EXTERNAL_ACCESS or UNSAFE): The assembly needs to be signed.

  13. Jaylen

    • 2019/9/19

    Whether you want to output the query results in SQL to XML or attempt to export the data in a SQL Server database to the XML file, 

  14. Yisroel

    • 2015/11/6

    Exporting results to XML · sql-server t-sql xml. I am trying to create an XML file from a SQL query. The out put 

  15. Daxton

    • 2015/10/18

    This section provides examples that use SQL/XML function XMLElement . Example 18-1 uses XMLElement to generate an XML date with a format that is different from 

  16. Milan

    • 2019/2/24

    How do I take an SQL query result and create an XML file? I want to execute an SQL query and put the results into an XML variable. Once the XML 

Comments are closed.

Recent Posts