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.
[Run Sample] | [View Source] |
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.
Dim sConnection as String = "server=(local)\\NetSDK;Integrated Security=SSPI;database=northwind"
Dim mySqlConnection as SqlConnection = new SqlConnection(sConnection)
Dim mySqlCommand as SqlCommand = 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.
Dim myDataSet1 as DataSet = new DataSet()
myDataSet1.ReadXml(mySqlCommand.ExecuteXmlReader(), XmlReadMode.Fragment)
' Modify to match the other dataset
myDataSet1.DataSetName = "NewDataSet"
' Get the same data through the provider.
Dim mySqlDataAdapter as SqlDataAdapter = new SqlDataAdapter("select * from customers", sConnection)
Dim myDataSet2 as DataSet = new DataSet()
mySqlDataAdapter.Fill(myDataSet2)
' Write data to files: data1.xml and data2.xml.
myDataSet1.WriteXml("data1.xml")
myDataSet2.WriteXml("data2.xml")
Console.WriteLine ("Data has been written 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())