Sorry, my stylesheet got cut off in the last post. Here it is...
<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>
</TR>
<TR>
<TD align="center" width="100%">
<TABLE BORDER="0" width="400">
<xsl:apply-templates select="OrderDetail"/>
</TABLE>
</TD>
</TR>
</xsl:template>
<xsl:template match="OrderDetail">
<TR><TD valign="top" width="50"><EM>Product:</EM></TD>
<TD valign="top" width="250"><xsl:value-of select="ProductName"/></TD>
<TD valign="top" width="50" ><EM>Price:</EM></TD>
<TD valign="top" width="50" >$<xsl:value-of select="UnitPrice"/></TD>
</TR>
</xsl:template>
</xsl:stylesheet>>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"))
>
> 'Return Hierarchical dataset
> 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
