XSL Loop inside a loop

Hi

I have generated an XML file using SSRS (see attached)
Raw.xml

I'm trying to get loop inside loop so (see code below)
<xsl:for-each select="//*[local-name()='ResultCategoryNode']">
					<graph3>
					<xsl:attribute name="Name">
						<xsl:value-of select="@ResultFieldCategory"/>
					</xsl:attribute>
						<xsl:for-each select="(//*[local-name()='ResultCategoryNode'])[@ResultFieldCategory=??ValuesFromOuterLoop??]">
						<graph>
							<xsl:attribute name="name">
								<xsl:value-of select="@ReportFieldMonth"/>
							</xsl:attribute>
							<xsl:attribute name="value">
								<xsl:value-of select="@ReportFieldValue"/>
							</xsl:attribute>
						</graph>
					</xsl:for-each>

Open in new window

which is incomplete and not working .

I have Category which should generate a tag and inside it should have its own data (details) so the end result should be like that (see  code below)  
 
<graph1 Name="Waste">
	<graph name="Jun 2010" value="8" />
	<graph name="July 2010" value="4" />
	<graph name="Aug 2010" value="0" />
	<graph name="Sep 2010" value="0" />
	<graph name="Oct 2010" value="0" />
	<graph name="Nov 2010" value="8" />
</graph1>
<graph2 Name="Water">
	<graph name="Jun 2010" value="34" />
	<graph name="July 2010" value="40" />
	<graph name="Aug 2010" value="1" />
	<graph name="Sep 2010" value="2" />
	<graph name="Oct 2010" value="38" />
	<graph name="Nov 2010" value="4" />
</graph2>

Open in new window


Many thanks in advance
Emil
LVL 10
itcoupleAsked:
Who is Participating?
 
Geert BormansConnect With a Mentor Information ArchitectCommented:

<?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="@ResultFieldCategory"/>
            </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>

Open in new window

0
 
itcoupleAuthor Commented:
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>

Open in new window

0
 
Geert BormansInformation ArchitectCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
itcoupleAuthor Commented:
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
0
 
Geert BormansInformation ArchitectCommented:
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
0
 
itcoupleAuthor Commented:
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>

Open in new window

LineGraphWidget-Raw.xml
0
 
Geert BormansInformation ArchitectCommented:
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>

Open in new window

0
 
itcoupleAuthor Commented:
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.
0
 
Geert BormansInformation ArchitectCommented:
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
0
 
itcoupleAuthor Commented:
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>

Open in new window

0
 
Geert BormansInformation ArchitectCommented:
welcome, goodluck with your efforts
0
 
itcoupleAuthor Commented:
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
0
 
Geert BormansInformation ArchitectCommented:
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
0
 
itcoupleAuthor Commented:
Hi

I will use the old approach first and will try to play with new code after that. I have posted new question here http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_26610251.html

Many thanks for your replies
Emil
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.