Please can someone help me on a little XSL problem?
I do use the book Professional SQL Server 2000 Programming, Robert Vieira. (Great book by the way!).

It is about a small project which looks like as the example given in chapter 20, Integration of XML into SQL Server, page 694, Parameterizing Templates. In this SQL example in the FROM clause there are two tables used (Customers and Orders) which is great.
What I can´t get up and running is how does a XSL stylesheet would looked like with this code. With a single table used that is not a problem but what I don´t get up and running is how to add the second table in XSL such that eg. the output would look like something like the picture given on page 715, such that it will output HTML from the fields of both tables.

Thanks for any reply!

I don't have the book, but if you can post the SQL and the XSL (or just a snippet if its v big) then I can help you.

rgds
Phil

From : Bernard Mulder
Date : 25 feb 2005
About : XML, XSL Northwind example

CONFIGURATION:

- IIS
  * E:\Inetpub

- Setting up HTTP Access
  * IIS Virtual Directory Management for SQL Server
  * New Virtual Directory
  * (General Tab) Virtual Directory Name
    Northwind
  * (General Tab) Local Path
    E:\Inetpub\NWind
  * (Security Tab) SQL Server
  * (Data Source Tab) SQL Server
    (local)
  * (Data Source Tab) Database
    Northwind
  * (Settings Tab) Options
    Allow template queries
  * (Virtual Names Tab) Defined virtiual names:
    New
    Virtual name: templates
    Type: template
    Path: E:\XMLDemo\templates
    Save
  * Apply
  * Ok

- SQL code WITHOUT XSL call

  <Root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:header>
      <sql:param name="CustomerID">ALFKI</sql:param>
    </sql:header>
    <sql:query>
      SELECT Customers.CustomerID,
        Customers.CompanyName,
        Orders.OrderID,
        Orders.OrderDate
      FROM Customers
      JOIN Orders
        ON Customers.CustomerID = Orders.CustomerID
      WHERE Customers.CustomerID = @CustomerID
      ORDER BY Customers.CustomerID
      FOR XML AUTO
    </sql:query>
  </Root>


- Browser URL XML
  http://proto/Northwind/templates/customers.xml

- Browser URL XML output
  - <Root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    - <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
        <Orders OrderID="10643" OrderDate="1997-08-25T00:00:00" />
        <Orders OrderID="10692" OrderDate="1997-10-03T00:00:00" />
        <Orders OrderID="10702" OrderDate="1997-10-13T00:00:00" />
        <Orders OrderID="10835" OrderDate="1998-01-15T00:00:00" />
        <Orders OrderID="10952" OrderDate="1998-03-16T00:00:00" />
        <Orders OrderID="11011" OrderDate="1998-04-09T00:00:00" />
      </Customers>
    </Root>

- So far No problem

- SQL code WITH XSL call

  <Root xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="customers.xsl">
    <sql:header>
      <sql:param name="CustomerID">ALFKI</sql:param>
    </sql:header>
    <sql:query>
      SELECT Customers.CustomerID,
        Customers.CompanyName,
        Orders.OrderID,
        Orders.OrderDate
      FROM Customers
      JOIN Orders
        ON Customers.CustomerID = Orders.CustomerID
      WHERE Customers.CustomerID = @CustomerID
      ORDER BY Customers.CustomerID
      FOR XML AUTO
    </sql:query>
  </Root>

- customers.xsl
  <?xml version='1.0' encoding='UTF-8'?>
  <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">

      <xsl:template match = '*'>
          <xsl:apply-templates />
      </xsl:template>
      <xsl:template match = 'Customers'>
         <TR>
           <TD valign="top"><a>
            <xsl:attribute name="href">../../Login.asp?CustomerID=
             <xsl:value-of select='@CustomerID'/>
           </xsl:attribute>
           <xsl:value-of select="@CustomerID"/></a></TD>
           <TD align="left" width="300"><B>
        <xsl:value-of select = '@CompanyName'/><br/>
        <xsl:value-of select='@Address'/><br/>
         </B>
    </TD>
         </TR>
      </xsl:template>
      <xsl:template match = '/'>
        <HTML>
          <HEAD>
      <TITLE>Northwind Traders Customers</TITLE>
          </HEAD>
          <BODY>
      <TABLE cellpadding="0" cellspacing="0" border="0">
        <TR>
          <TD>
          <IMG alt="Northwind Traders" src="NorthwindLogo.jpg"
            border="0"/>
         </TD>
          <TD> Northwind Traders Online
          </TD>
        </TR>
        <TR>
          <TD bgcolor="Aqua" colspan="2">

        </TD>
        </TR>
        <TR>
        <TD valign="top" bgcolor="Aqua">
        </TD>
        <td>
        <TABLE border='1' width="*">
                 <TR><TH colspan='2'>Customers</TH></TR>
                 <TR><TH>Customer ID</TH><th>Company</th></TR>
                 <xsl:apply-templates select = 'Root' />
             </TABLE>
      </td>
    </TR>
  </TABLE>
      </BODY>
        </HTML>
      </xsl:template>
  </xsl:stylesheet>

- Browser URL XML, XSL
http://proto/Northwind/templates/cus...type=text/html

- Browser URL XML XSL output
  A nice table with one name. Ok I did not put the orders fields in the customers.xsl because I don't know how to put the Orders table in it.

THANKS! For you reply and Help!!!

OIC, this is really just an XSL question then isn't it.

As I don't have the book I don't know what the table on page 715 looks like, but in general there are 2 ways you can amend customers.xsl to incorporate data from the related Orders table:
1. inside your <xsl:template match="Customers"> you can reference items from the <Orders> child nodes directly, for example if you wanted to add another table cell showing a comma separated list of each customer's orders you could just add something like this:
2. alternatively you could just create another template containing whatever output you wanted from the Orders, maybe an embedded table underneath each customer with details of the orders:
which you incorporate within your existing Customers template with an xsl:apply-templates call, something like this:
does that help?
Phil

Phil you are the BEST!!!

Many many thanks!!! IT is WORKING :D

Best regards Bernard

Good stuff. happy to help.

The XSL code transformed, just make it look a little better:

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

    <xsl:template match = '*'>
        <xsl:apply-templates />
    </xsl:template>

    <xsl:template match = 'Customers'>
    <TR>
        <TD valign="top"><A>
            <xsl:attribute name="href">../../Login.asp?CustomerID=
                <xsl:value-of select='@CustomerID'/>
            </xsl:attribute>

            <xsl:value-of select="@CustomerID"/></A></TD>
            <TD align="left" width="300"><B>
            <xsl:value-of select = '@CompanyName'/><BR/>
            <xsl:value-of select='@Address'/></B>

            <xsl:for-each select="Orders">
            <TR>
                <TD>
                    <TD>
                        <TD>
                            <xsl:value-of select="@OrderID"/><BR/>
                            <xsl:if test="position()!=last()"> </xsl:if>
                        </TD>

                        <TD>
                            <xsl:value-of select="@OrderDate"/><BR/>
                            <xsl:if test="position()!=last()"> </xsl:if>
                        </TD>
                    </TD>
                </TD>
            </TR>
            </xsl:for-each>

        </TD>
    </TR>
    </xsl:template>

    <xsl:template match = '/'>
    <HTML>
        <HEAD>
            <TITLE>Northwind Traders Customers</TITLE>
        </HEAD>

        <BODY>
            <TABLE cellpadding="0" cellspacing="0" border="0">
                <TR>
                    <TD Width="20">
                    </TD>
                    <TD>
                         Northwind Traders Online
                    </TD>
                </TR>
                <TR>
                    <TD bgcolor="Yellow" colspan="3">

                    </TD>
                </TR>
                <TR>
                    <TD valign="top" bgcolor="Yellow">
                    </TD>
                      <TD>
                        <TABLE border='1' width="*">
                            <TR>
                                <TH colspan='4'>Customers
                                </TH>
                            </TR>
                            <TR>
                                <TH>Customer ID</TH>
                                <TH>Company</TH>
                                <TH>Order ID</TH>
                                <TH>Date</TH>
                            </TR>
                            <xsl:apply-templates select = 'Root' />
                        </TABLE>
                    </TD>
                </TR>
            </TABLE>
        </BODY>
    </HTML>
    </xsl:template>

</xsl:stylesheet>

- The output looks like this:
www.miramardrenthe.nl/img/nwind.bmp