Solved

XSL Loop inside a loop

Posted on 2010-11-09
14
253 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:itcouple
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
ID: 34093545

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

Author Comment

by:itcouple
ID: 34093945
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 34095616
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 10

Author Comment

by:itcouple
ID: 34099893
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 34100604
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
 
LVL 10

Author Comment

by:itcouple
ID: 34102899
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 34103048
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
 
LVL 10

Author Comment

by:itcouple
ID: 34103324
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 34103382
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
 
LVL 10

Author Comment

by:itcouple
ID: 34110347
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 34110977
welcome, goodluck with your efforts
0
 
LVL 10

Author Comment

by:itcouple
ID: 34113053
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 34113116
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
 
LVL 10

Author Comment

by:itcouple
ID: 34118544
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question