Solved

XSL Loop inside a loop

Posted on 2010-11-09
14
244 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
  • 7
  • 7
14 Comments
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
Comment Utility

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

Author Comment

by:itcouple
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 10

Author Comment

by:itcouple
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
welcome, goodluck with your efforts
0
 
LVL 10

Author Comment

by:itcouple
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now