Using SQLXML and .Net for data integration

“The SqlClient data provider supports ExecuteXmlReader, a provider-specific method on the SqlCommand class. Rather than provide a SqlDataReader to process the result of a SQL query, ExecuteXmlReader produces an XmlReader, which can be used to directly consume the results of a SELECT … FOR XML query. The XmlReader might be used directly—for example, to serialize the resulting document to a Stream for transmission to a BizTalk server. The document could be serialized to disk by using an XmlTextReader. It could be read directly into the DataSet by using the DataSet’s ReadXml method. Listing 7–39 shows an example. The interesting point of ExecuteXmlReader is that, if you use a FOR XML query that produces nested hierarchies of XML output (AUTO or EXPLICIT mode), it takes only a single SELECT statement to produce multiple Data-Tables with the appropriate DataRelations in the DataSet.
Listing 7–39 Using SQLXML through ExecuteXmlReader

SqlConnection conn = new SqlConnection(
“server=.;uid=sa;database=pubs”);
SqlCommand cmd = new SqlCommand(
“select * from authors for xml auto, xmldata”,
conn);
conn.Open();

XmlTextReader rdr;
rdr = (XmlTextReader)cmd.ExecuteXmlReader();

DataSet ds = new DataSet();
ds.ReadXml(rdr,
XmlReadMode.Fragment);

When using ExecuteXmlReader to obtain an XmlReader followed by DataSet.ReadXml to populate a DataSet, you must take certain precautions because the XML produced by SQL Server does not contain a root element. To obtain all the XML nodes, you must use XmlReadMode.XmlFragment, a special XmlReadMode. In addition, you must either prepopulate the DataSet’s schema with information that matches the incoming fragment or use the XMLDATA keyword in your SQL statement to prepend an XDR schema to your fragment.”

Read the full article from InformIT.