How Do I...? Common Tasks QuickStart Tutorial

ADO.NET: Generate XML with data from SQL Server

This sample illustrates how to produce XML from SQL Server using two different techniques. The first uses the ExecuteXmlReader method of SqlCommand to get an XmlTextReader, which is then populated into a DataSet using the ReadXml method of the DataSet class. The second uses the SqlDataAdapter to extract the data content and adapt it to the XML structure in the DataSet. The SqlDataAdapter class encapsulates a set of data commands and a database connection that is used to fill the DataSet and potentially update the data source.

C# xmlfromsqlsrv.aspx

This sample begins by opening a connection to the SQL Server Northwind database. Then a SqlCommand is created that will select all rows from the Customers table of the Northwind database. In this command the FOR XML clause is used to indicate you are requesting the SQL Server return the results of the query as an XML document. The XMLDATA flag on the FOR XML clause specifies that an XML-Data schema should be returned. The schema is prepended to the document as an inline schema.

		

    String sConnection = "server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind";
    SqlConnection mySqlConnection = new SqlConnection(sConnection);
    SqlCommand mySqlCommand = new SqlCommand("select * from customers FOR XML AUTO, XMLDATA", mySqlConnection);
    mySqlCommand.CommandTimeout = 15;
    ...
    mySqlConnection.Open();
    

In the first section of the sample a DataSet called myDataSet1 is created. Then, you add an XmlTextReader and the XmlReadMode.Fragment flag to read the XmlDocument returned from SQL Server into myDataSet1 using the ReadXml method.

In the second section of the sample a DataSet called myDataSet2 is created and a SqlDataAdapter is created to provide a SQL command to access and retrieve the data from the SQL server database. Then the Fill method is called on the SqlDataAdapter to load the data into myDataSet2.

The final step is to write out both datasets to validate that the data was the same and review the XML format.

		
    // Now create the DataSet and fill it with xml data.
    DataSet myDataSet1 = new DataSet();
    myDataSet1.ReadXml((XmlTextReader)mySqlCommand.ExecuteXmlReader(), XmlReadMode.Fragment);
    
    // Modify to match the other dataset
    myDataSet1.DataSetName = "NewDataSet";
    
    
    // Get the same data through the provider.
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("select * from customers", sConnection);
    DataSet myDataSet2 = new DataSet();
    mySqlDataAdapter.Fill(myDataSet2);
    
    // Write data to files: data1.xml and data2.xml for comparison.
    myDataSet1.WriteXml("data1.xml");
    myDataSet2.WriteXml("data2.xml");
    Console.WriteLine ("Data has been writen to the output files: data1.xml and data2.xml");
    Console.WriteLine ();
    Console.WriteLine ("********************data1.xml********************");
    Console.WriteLine (myDataSet1.GetXml());
    Console.WriteLine ();
    Console.WriteLine ("********************data2.xml********************");
    Console.WriteLine (myDataSet2.GetXml());