Yes, you can output the data inside the dataset as hierarical XML if you set up the data relations to your data tables. You can use the WriteXML method of the dataset to do this, however, if you want to control the XML that is outputted, you can use the XmlDataDocument object.
Here is an example of writing XML from a dataset with relations to the browser. If the browser is IE, I just send the XML down with a link to the XSL stylesheet. Otherwise I do the transformation into HTML on the server. I created this example from the Northwind SQL-Server database - Customers, Orders and Order Details are selected for 'ALFKI' (our old friend <g>) .
< %@ Import Namespace="System.Web" %>
< %@ Import Namespace="System.Xml" %>
< %@ Import Namespace="System.Xml.Xsl" %>
< %@ Import Namespace="System.Data" %>
< %@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim ds As DataSet = GetData()
Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
Dim writer As XmlTextWriter = New XmlTextWriter(Response.OutputStream, System.Text.Encoding.UTF8)
If Request.Browser.Browser = "IE" Then
writer.Formatting = Formatting.Indented
writer.Indentation = 5
Dim PItext As String = "type='text/xsl' href='stylesheets/mystylesheet.xsl'"
writer.WriteStartDocument()
writer.WriteProcessingInstruction("xml:stylesheet", PItext)
xmlDoc.Save(writer)
Else
Dim xslTran As XslTransform = New XslTransform()
xslTran.Load(MapPath("StyleSheets") + "\mystylesheet.xsl")
xslTran.Transform(xmlDoc, Nothing, writer)
End If
writer.close()
End Sub
Private Function GetData() As DataSet
Dim strSQL As String = _
"Select Customers.* From Customers Where Customers.CustomerID = 'ALFKI' " & _
"; Select Orders.* From Orders Where Orders.CustomerID = 'ALFKI' " & _
"; Select [Order Details].*, Products.ProductName " & _
" From [Order details], Products, Orders " & _
" Where Products.ProductID = [Order Details].ProductID AND " & _
" Orders.OrderID = [Order Details].OrderID AND " & _
" Orders.CustomerID = 'ALFKI' "
Dim dsOrders As New DataSet()
Dim cnn As New SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI")
Dim cmd As New SqlCommand(strSQL, cnn)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dsOrders)
dsOrders.Tables(0).TableName = "Customer"
dsOrders.Tables(1).TableName = "Order"
dsOrders.Tables(2).TableName = "OrderDetail"
dsOrders.Relations.Add("Customer_Order", _
dsOrders.Tables("Customer").Columns("CustomerID"), _
dsOrders.Tables("Order").Columns("CustomerID"))
dsOrders.Relations.Add("Order_Detail", _
dsOrders.Tables("Order").Columns("OrderID"), _
dsOrders.Tables("OrderDetail").Columns("OrderID"))
dsOrders.Relations("Customer_Order").Nested = True
dsOrders.Relations("Order_Detail").Nested = True
dsOrders.DataSetName = "CustomerOrders"
Return dsOrders
End Function
</script>Here is the stylesheet I used (containted in the stylesheets subfolder).
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="CustomerOrders">
<HTML>
<STYLE>
BODY {font-family:verdana;font-size:9pt}
TD {font-size:8pt}
</STYLE>
<BODY>
<TABLE BORDER="0" >
<xsl:apply-templates select="Customer"/>
</TABLE>
</BODY>
</HTML>
</xsl:template>
<xsl:template match="Customer">
<TR>
<TD>Company:</TD>
<TD><xsl:value-of select="CompanyName"/></TD>
</TR>
<TR>
<TD>Contact Name:</TD>
<TD><xsl:value-of select="ContactName"/></TD>
</TR>
<TR>
<TD>ID:</TD>
<TD><xsl:value-of select="CustomerID"/></TD>
</TR>
<TR>
<TD colspan="2"><hr /><EM><B>Orders:</B></EM></TD>
</TR>
<TR>
<TD colspan="2">
<TABLE BORDER="0" >
<xsl:apply-templates select="Order"/>
</TABLE>
</TD>
</TR>
</xsl:template>
<xsl:template match="Order">
<TR>
<TD>
<B>Order: #<xsl:value-of select="OrderID"/><BR />Date:
<xsl:for-each select="OrderDate">
<xsl:value-of select="substring(text(),6,2)"/>/<xsl:value-of select="substring(text(),9,2)"/>/<xsl:value-of select="substring(text(),1,4)"/>
</xsl:for-each>
</B>
</TD>Have Fun!
-B
>Hey Paul,
>
> Thanks for the article. Is it possible to do the same thing by setting a relation in the dataset and writting the XML?
>
>Jim
>
>>>Hi Paul,
>>>
>>> Thanks for the response. What is the best way to write the XML file from within the web app?
>>>
>>
>>Try this link for one approach:
>>
>>
http://support.microsoft.com/default.aspx?scid=kb;en-us;301282&SD=MSDN
