XSLT Dynamic Columns (Save my eyebrows!)

[R144N]

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):

<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.

<?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