XSLT Dynamic Columns (Save my eyebrows!)

Discussion in 'XML' started by R144N, Oct 12, 2008.

  1. R144N

    R144N

    Joined:
    Oct 12, 2008
    Messages:
    2
    Hello,

    I'm desperately trying to get an XSLT template to work with an SQL 2005 XML Document. Ive run out of Hair on my head and have to resort to pulling out my eyebrows next unless someone can help me...

    Background Info:
    I have a Purchase Order Table which is linked to a Shipments Table (many-to-many) which is in turn linked to a Containers Table (many-to-many)

    The functionality given to the customer is that they can select which table columns they wish to have displayed in the HTML report from all three these tables.

    I have successfully been able to retrive the dynamic returnset from SQL as an XML Document.

    The problem is I have almost zero experience working with XSLT!!

    Here is the XML Document Structure Returned by SQL (With dummy values):

    Code:
    <Root>
    	<PurchaseOrders>
    		<PO_Num>12345</PO_Num>
    		<PO_Date>21 Jan 2008</PO_Date>
    		<Shipments>
    			<SH_Carrier>MSC</SH_Carrier>
    			<SH_Vessel>Titus</SH_Vessel>
    			<Containers>
    				<CN_Num>CN24680</CN_Num>
    				<CN_Commodity>Tinned Tuna</CN_Commodity>
    			</Containers>
    			<Containers>
    				<CN_Num>CN24680</CN_Num>
    				<CN_Commodity>Tinned Tuna</CN_Commodity>
    			</Containers>
    		</Shipments>
    		<Shipments>
    			<SH_Carrier>MSC</SH_Carrier>
    			<SH_Vessel>Titus</SH_Vessel>
    			<Containers>
    				<CN_Num>CN24680</CN_Num>
    				<CN_Commodity>Tinned Tuna</CN_Commodity>
    			</Containers>
    		</Shipments>
    	</PurchaseOrders>
    	<PurchaseOrders>
    		<PO_Num>12345</PO_Num>
    		<PO_Date>21 Jan 2008</PO_Date>
    		<Shipments>
    			<SH_Carrier>MSC</SH_Carrier>
    			<SH_Vessel>Titus</SH_Vessel>
    			<Containers>
    				<CN_Num>CN24680</CN_Num>
    				<CN_Commodity>Tinned Tuna</CN_Commodity>
    			</Containers>
    			<Containers>
    				<CN_Num>CN24680</CN_Num>
    				<CN_Commodity>Tinned Tuna</CN_Commodity>
    			</Containers>
    		</Shipments>
    		<Shipments>
    			<SH_Carrier>MSC</SH_Carrier>
    			<SH_Vessel>Titus</SH_Vessel>
    			<Containers>
    				<CN_Num>CN24680</CN_Num>
    				<CN_Commodity>Tinned Tuna</CN_Commodity>
    			</Containers>
    		</Shipments>
    	</PurchaseOrders>
    </Root>
    
    As mentioned, the customer can choose whether he wants for example: PO_Num, CN_Num, SH_Carrier etc. to display or not so I cannot hardcode these into the template.

    I have gotten as far as the following with my XSLT but you'll see there are a lot of flaws in it.

    Code:
    <?xml version="1.0" encoding="UTF-8" ?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    	<xsl:template match="/Root">
    		<table border="1">
    			<xsl:for-each select="PurchaseOrders">
    			<tr>
    				<td>
    					<table border="1">
    						<tr>
    							<xsl:for-each select="PurchaseOrders[1]/*">
    							<th>
    								<xsl:value-of select="name()"/>
    							</th>
    							</xsl:for-each>
    						</tr>
    						<tr>
    							<xsl:for-each select="PurchaseOrders/*">
    							<td>
    								<xsl:value-of select="local-item"/>
    							</td>
    							</xsl:for-each>
    						</tr>
    					</table>
    				</td>
    			</tr>
    			<tr>
    				<td>Shipments</td>
    			</tr>
    			<tr>
    				<td>
    					<table border="1">
    						<tr>
    							<xsl:for-each select="Shipments[1]/*">
    								<th>
    									<xsl:value-of select="local-name()"/>
    								</th>
    							</xsl:for-each>
    						</tr>
    						<xsl:for-each select="Shipments">
    							<tr>
    								<xsl:for-each select="Shipments/*">
    									<td>
    										<xsl:value-of select="local-item"/>
    									</td>
    								</xsl:for-each>
    							</tr>
    						</xsl:for-each>
    					</table>
    				</td>
    			</tr>
    		</xsl:for-each>
    		</table>
    	</xsl:template>
    </xsl:stylesheet>
    
    At this point - I don't know what to use to get the actual node value (I tried "Local-Item" which does not work. Sheepish Grin.

    The local-name() is also returning a header column for each "Shipment" node which is not what I want...

    Also the stylesheet is returning only the Shipments Column Headers "local-names()" but not the PurchaseOrders local-names anymore... I haven't even begun to insert Containers yet...(Eyebrow loss + eyebrow loss)

    Please save my eyebrows - they're all I have left...

    xRiaan
    R144N, Oct 12, 2008
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. helpful sql
    Replies:
    0
    Views:
    791
    helpful sql
    May 19, 2005
  2. ittay.dror@gmail.com

    database columns vs. gui columns and sorting

    ittay.dror@gmail.com, Feb 26, 2006, in forum: Java
    Replies:
    5
    Views:
    575
    ittay.dror@gmail.com
    Mar 4, 2006
  3. henrybranson@hotmail.com

    CSS columns problem - faux columns don't work

    henrybranson@hotmail.com, Nov 21, 2005, in forum: HTML
    Replies:
    4
    Views:
    921
    henrybranson@hotmail.com
    Nov 24, 2005
  4. =?Utf-8?B?YmVub2l0?=

    Read CSV - string Columns - Int columns

    =?Utf-8?B?YmVub2l0?=, May 8, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    425
    =?Utf-8?B?YmVub2l0?=
    May 8, 2006
  5. R144N
    Replies:
    0
    Views:
    477
    R144N
    Oct 12, 2008
Loading...

Share This Page