Creating a Command Object that Returns XML Data

You can also execute commands that return data formatted as XML. To execute commands that return XML data, create commands that run SQL statements that return

XML or retrieve existing XML-formatted data from your database. Set the CommandText property to a SQL statement that returns XML and call the ExecuteXMLReader method of the command. Calling the ExecuteXMLReader method returns a System.Xml.XmlReader object, which is similar to a data reader except that it is specifically for accessing XMLformatted data.

For example, to return the results of a SQL statement as well-formed XML, use code similar to the following:

Dim ExecuteXMLCommand As New SqlCommand ExecuteXMLCommand.Connection = NorthwindConnection ExecuteXMLCommand.CommandType = CommandType.Text

' Add the For XML Auto clause to return the data As well formed XML ExecuteXMLCommand.CommandText = "SELECT CustomerID FROM Customers For XML Auto"

ExecuteXMLCommand.Connection.Open()

Dim reader As System.Xml.XmlReader = ExecuteXMLCommand.ExecuteXmlReader

' Add code here to iterate through the XMLReader reader.Close()

ExecuteXMLCommand.Connection.Close() // C#

SqlCommand ExecuteXMLCommand = new SqlCommand(); ExecuteXMLCommand.Connection = NorthwindConnection; ExecuteXMLCommand.CommandType = CommandType.Text;

// Add the For XML Auto clause to return the data as well formed XML ExecuteXMLCommand.CommandText = "SELECT CustomerID FROM Customers For XML Auto";

ExecuteXMLCommand.Connection.Open();

System.Xml.XmlReader reader = ExecuteXMLCommand.ExecuteXmlReader(); // Add code here to iterate through the XMLReader; reader.Close();

ExecuteXMLCommand.Connection.Close();

Was this article helpful?

0 0

Post a comment