Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First posted :
03/24/2008
Times viewed :
230
.NET XmlTextWriter
SQL Server 2000 introduced the FOR XML extensions to SQL. We no longer have
to write large amounts custom code for each XML type. All we need to do know is
to carefully craft our SQL queries and SQL Server would do the conversion for
us:
public static void GetSQLResultsAsXml( string query,
SqlCommand cmd = new SqlCommand( query, connection );
XmlReader reader = cmd.ExecuteXmlReader();
writer.WriteNode( reader );
}
It is quite obvious that this approach results in far less code, than
transforming the results by hand. Still we have to custom develop queries for
each data type, but at least we only have to supply the query statements. The
data access code can remain in one generic method and SQL Server and does the
XML transformation for us. The downside is that we lose some of the flexibility
as to what we can map from the database to XML. We are constrained by what SQL
Server can return with one single query. In the case of FOR XML EXPLICIT mode
queries we can create quite complex XML structures with one single query, but
they are quite complicated to develop and maintain. From the performance
perspective, the combination of SQL Server and the XmlTextWriter also delivers
the XML data quite speedy.
