Many a times I have come across this problem which developers face when they want the XML file to be created dynamically from database.
For catering this problem first of all create a table in database which have at least five columns with names ImageUrl,NavigateUrl,AlternateText,Keyword,Impressions.
Don’t worry if you have already created a database table and given your custom names to columns and are already using the database and creating XML file by lot of coding efforts.
With SQL server 7.0 and 2000 Microsoft started supporting XML input and xml output from the database using ADO.Net classes. This branch of ADO.Net is also known as SQL XML.
SQLXML primarily make use of three modes to fetch data from database along with FOR clause, which are: -
RAW
AUTO
EXPLICIT
We are going to discuss RAW and Auto and then carry on with our sample.
1.) FOR XML RAW Clause
RAW mode queries result in a flat table-like XML format. The format does not preserve any information about the origin of the data or hierarchical relationships. SQL Server simply transforms each row of the result set into an XML element with the name row, very similar to the output format of the persist-to-XML option of classic ADO Recordsets. Every column that is not NULL is mapped to an attribute of the column’s name. The example SQL statement below illustrates using XML RAW:
select ImageUrl,NavigateUrl,AlternateText,Keyword,Impressions from AdRotator1 as ad for xml RAW
A row returned from an FOR XML RAW query could look like this element:
<row>
<ImageUrl>image/asp.gif</ImageUrl>
<NavigateUrl>http://www.sam.net </NavigateUrl>
<AlternateText>test</AlternateText>
<Keyword>te</Keyword>
<Impressions>60</Impressions>
</row>
The element names do not reflect the tables names the column are coming from. It only lists the columns we selected.
2.) FOR XML AUTO Clause
The XML format returned via AUTO clause is more descriptive. Each selected row results in an element named after the table from which it was selected. The selected columns result in attributes of the elements by default. If the SELECT statement joins multiple tables the results from the joined table are represented as child elements. AUTO mode also recognizes the ELEMENTS option. If we append “, ELEMENTS” to the query statement columns in the returned rowset are also mapped to child elements rather than attributes. The nesting of elements and their children is determined by the order of the tables in the SELECT clause. Thus the order of the columns in the SELECT clause is significant. We can see the difference if we replace RAW with AUTO in the above query:
select ImageUrl,NavigateUrl,AlternateText,Keyword,Impressions from AdRotator1 as Ad for xml auto,elements,ROOT('Advertisements')
<Ad>
<ImageUrl>image/asp.gif</ImageUrl>
<NavigateUrl>http://www.sam.net </NavigateUrl>
<AlternateText>test</AlternateText>
<Keyword>te</Keyword>
<Impressions>60</Impressions>
</Ad>
Now lets carry on with the sample.
Just run this piece of code and bingo.
SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\shakti\My Documents\Visual Studio 2005\Projects\Spider\WindowsApplication1\Database1.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand("select ImageUrl,NavigateUrl,AlternateText,Keyword,Impressions from AdRotator1 as ad for xml auto,elements,ROOT('Advertisements')", con);
con.Open();
System.Xml.XmlReader reader = cmd.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
doc.Load(reader);
doc.Save("C:\\testAd.xml");
reader.Close();
Figure 2 Creating an ExtensionAttribute Class