XSL Loop inside a loop

Many thanks

After 6.5h of struggle and just before getting your solution I managed to do that by cheating..... I duplicated my field so I have it on higher and lower level and used the attached code (after stealing your contactenation as I struggled with that as well)


Many thanks for your code. Once I have it I will practise with it and probably replace my existing one to avoid workarounds.

Regards
Emil

<graphholder>
				<xsl:for-each select="(//*[local-name()='ResultCategoryNode'])">

					<xsl:variable name="MyCategory" select="@ResultCategoryFieldCategory" />
					
					<xsl:element name="{concat('graph', position())}">
						<xsl:attribute name="Name">
							<xsl:value-of select="@ResultCategoryFieldCategory"/>
						</xsl:attribute>
						
						<xsl:for-each select="(//*[local-name()='ResultDetailsNode'])[@ResultFieldCategory=$MyCategory]">
						<graph>
								<xsl:attribute name="Value">
									<xsl:value-of select="@ResultFieldNoOfJobs"/>
								</xsl:attribute>
						</graph>
					</xsl:for-each>
					</xsl:element>

				</xsl:for-each>
			</graphholder>

Select allOpen in new window

Actually, your code only had one error in it
<xsl:for-each select="(//*[local-name()='ResultDetailsNode'])[@ResultFieldCategory=$MyCategory]">
starts from the root of the document again
you fixed it by cheating indeed
but your fix is a sad one, because you only want the nodes that are childs of your current context
and you are evaluating the entire document over and over again.
This is a real performance drain
You could fix your code by dropping the $MyCategory in favour of a relative XPath instead of an absolute one
<xsl:for-each select=".//*[local-name()='ResultDetailsNode']">
This will be a lot faster.
That is basically the only error I solved.

The other thing I did is getting rid of the for-each constructs.
Developing XSLT, apply-templates and seperate templates are your friends.
They will make your code a whole lot easier to maintain afterwards

Hi Gertone,

Many thanks for your constructive feedback. I will go with you solution when I have a minute and continue using it for the future.

p.s. This is was my very first XSLT code in my life! and the technology is completelly new to me, so again many thanks for the samples and suggestions!

Regards
Emil

Hi Emil,

Welcome
one other thought
*[local-name()='ResultCategoryNode']
is a construct that you would use when ResultCategoryNode is in an unknown namespace
your example has no namespace in it
In real life, this construct is rarely required since
- either the namespace is known so you can bind it to a prefix and use any:ResultCategoryNode
- or there is no namespace and you can simply use ResultCategoryNode
I think in this case you could use the later, and it will significantly increase performance on your stylesheet

Not too bad as a first stylesheet by the way :-)

cheers

Geert

Hi Geert,

I've tried the code on my slightly changed report but I'm getting this error
"Only one top level element is allowed in an XML document. Error processing resource "

It is probably related to the slightly changed XML (+ change of node name (x1) but I cannot work out where I need to make the changes. I've tried guessing abd added top level tag but it comaplain about missing namespace.

I'm attaching the final XML file and below also your code with one change in name (ResultFieldCategory->ResultCategoryFieldCategory --- which was done to avoid the same names, probably redundant with this approach)

I will try my "skills" (google it) to solve that.... which might take a long while.... so I would greatly appreciate your solution to that :)

Many thanks in advance
Emil

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
	<xsl:output indent="yes"/>
	<xsl:template match="/">
		<xsl:apply-templates select="//*[local-name()='ResultCategoryNode']"/>
	</xsl:template>
		<xsl:template match="*[local-name()='ResultCategoryNode']">
			<xsl:element name="{concat('graph', position())}">
				<xsl:attribute name="Name">
					<xsl:value-of select="@ResultCategoryFieldCategory"/>
				</xsl:attribute>
				<xsl:apply-templates select=".//*[local-name()='ResultDetailsNode']">
				</xsl:apply-templates>
			</xsl:element>
		</xsl:template>
		<xsl:template match="*[local-name()='ResultDetailsNode']">
			<graph>
				<xsl:attribute name="name">
					<xsl:value-of select="@ResultFieldMonth"/>
				</xsl:attribute>
				<xsl:attribute name="value">
					<xsl:value-of select="@ResultFieldNoOfJobs"/>
				</xsl:attribute>
			</graph>
		</xsl:template>
</xsl:stylesheet>

Select allOpen in new window

LineGraphWidget-Raw.xml

I introduced the namespace, so this will run substantially faster
I introduced a container element, now the resulting XML is wellformed, and you will not get that particular error anymore
I introduced some smart numbering for the graph elements
<graph1> => <graph001> to cater for 999 graph elements, not ruining a potential sorting

Note thta personally I believe that you should use
<graph position="001">
instead of
<graph001>

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:lgw="LineGraphWidget"
     exclude-result-prefixes="lgw"
    version="1.0">
    <xsl:output indent="yes"/>
    <xsl:template match="/">
        <xsl:element name="graphs">
            <xsl:apply-templates select="//lgw:ResultCategoryNode"/>
        </xsl:element>
    </xsl:template>
    <xsl:template match="lgw:ResultCategoryNode">
        <xsl:element name="{concat('graph', format-number(position(), '000'))}">
            <xsl:attribute name="Name">
                <xsl:value-of select="@ResultCategoryFieldCategory"/>
            </xsl:attribute>
            <xsl:apply-templates select=".//lgw:ResultDetailsNode">
            </xsl:apply-templates>
        </xsl:element>
    </xsl:template>
    <xsl:template match="lgw:ResultDetailsNode">
        <xsl:element name="graph">
            <xsl:attribute name="name">
                <xsl:value-of select="@ResultFieldMonth"/>
            </xsl:attribute>
            <xsl:attribute name="value">
                <xsl:value-of select="@ResultFieldNoOfJobs"/>
            </xsl:attribute>
        </xsl:element>
    </xsl:template>
</xsl:stylesheet>

Select allOpen in new window

That worked perfectly. I just need to find a tutorial and check out how that actually works :)

Many thanks you saved my day!
Emil

p.s. Regarding Graphs numbers that is something I need to comply with but will make the suggestion with reasoning behind it.

OK, so if graph001 instead of graph1 is NOT a good idea,
replace
<xsl:element name="{concat('graph', format-number(position(), '000'))}">
with
<xsl:element name="{concat('graph', position())}">
as it was before

as a start this one is nice
http://nwalsh.com/docs/tutorials/xsl/xsl/frames.html
it is very old, but still relevant

Many thanks for the link.

I have added 'config' to my file based on your code and it was painless :) now is time to create several new XSLT file so probably I will post new question on EE when I get stuck.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:lgw="LineGraphWidget"
     exclude-result-prefixes="lgw"
    version="1.0">
	<xsl:output indent="yes"/>
	<xsl:template match="/">
		<xsl:element name="graphs">
			<xsl:element name="graphconfig">
				<xsl:attribute name="colorSet">
					<xsl:value-of select="'mixColors'"/>
				</xsl:attribute>
				<xsl:attribute name="minVal">
					<xsl:value-of select="0"/>
				</xsl:attribute>
				<xsl:attribute name="maxVal">
					<xsl:value-of select="100"/>
				</xsl:attribute>
				<xsl:attribute name="inte">
					<xsl:value-of select="50"/>
				</xsl:attribute>
				<xsl:apply-templates select="//lgw:Details1"/>
			</xsl:element>
			<xsl:element name="graphholder"> 
				<xsl:apply-templates select="//lgw:ResultCategoryNode"/>
			</xsl:element>
		</xsl:element>
	</xsl:template>
	<xsl:template match="lgw:Details1">
		<xsl:element name="graph">
			<xsl:attribute name="name">
				<xsl:value-of select="@ConfigMonth"/>
			</xsl:attribute>
		</xsl:element>
	</xsl:template>
	<xsl:template match="lgw:ResultCategoryNode">
		<xsl:element name="{concat('graph', position())}">
			<xsl:attribute name="Name">
				<xsl:value-of select="@ResultCategoryFieldCategory"/>
			</xsl:attribute>
			<xsl:apply-templates select=".//lgw:ResultDetailsNode">
			</xsl:apply-templates>
		</xsl:element>
	</xsl:template>
	<xsl:template match="lgw:ResultDetailsNode">
		<xsl:element name="graph">
			<xsl:attribute name="value">
				<xsl:value-of select="@ResultFieldNoOfJobs"/>
			</xsl:attribute>
		</xsl:element>
	</xsl:template>
</xsl:stylesheet>

Select allOpen in new window

welcome, goodluck with your efforts

Gertone,

Quick question. Do you have experience with SQL Server and XSLT?

I've tried the code on SQL Server 2008 R2 and works fine but with 2008 I get some results (tags with static data) but no related to the XML.

The ugly for each approach seems to work on both version. We will move to R2 in Jan but everything is prepared on 2008 to test the results with Flash widgets

Any ideas???? (Probably bug with SSRS 2008)

Regards
Emil

Wild guess.
If the for-each approach works with the new release, and the latest code doesn't.
Then you might have a problem with the namespace.
It could be that the export namespace of SQL server changed

Hi

I will use the old approach first and will try to play with new code after that. I have posted new question here https://www.experts-exchange.com/questions/26610251/XSLT-issue-with-SSRS-2008-works-fine-with-2008-R2.html

Many thanks for your replies
Emil