Solved

XSLT 1.0 grouping

Posted on 2013-06-04
5
312 Views
Last Modified: 2013-06-06
I am trying to create an XSLT (1.0) that transforms information for the following schemas:

Source Schema

<Accounts><Account><AccountNumber/><ServiceCode/><StartDate/><EndDate/></Account></Accounts>

Open in new window


Destination Schema

<ServiceCodes><ServiceCode><AccountNumber/><Code/><StartDate/><EndDate/></ServiceCode></ServiceCodes>

Open in new window


These are simplified schemas but they represent the root of my issue.  Please not that there can be multiple "Account" nodes in the source and multiple "ServiceCode" nodes in the destination.  I can easily map these two schemas using XSLT.  There are times when more than one "Account" nodes will be present for the same "ServiceCode" but the start and end dates are different.  In this situation, I want to update the "Code" node in the destination to reflect something like "--SERVICECODE FROM SOURCE-- PLEASE REVIEW DUE TO DATE MISMATCH."  Can anyone help with this?  Thank you!
0
Comment
Question by:cperry-sca
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39220776
You could use a key

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    <xsl:key name="account" match="Account" use="ServiceCode"/>
    
    
    <xsl:template match="StartDate">
        <xsl:variable name="this-date" select="."/>
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:choose>
                <xsl:when test="key('account', ../ServiceCode)[not(StartDate = $this-date)]">
                    <xsl:text>--SERVICECODE FROM SOURCE-- PLEASE REVIEW DUE TO DATE MISMATCH.</xsl:text>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:apply-templates select="node()"/>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:copy>
    </xsl:template>
    
    <xsl:template match="EndDate">
        <xsl:variable name="this-date" select="."/>
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:choose>
                <xsl:when test="key('account', ../ServiceCode)[not(EndDate = $this-date)]">
                    <xsl:text>--SERVICECODE FROM SOURCE-- PLEASE REVIEW DUE TO DATE MISMATCH.</xsl:text>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:apply-templates select="node()"/>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:copy>
    </xsl:template>
    
    
    <xsl:template match="node()">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </xsl:copy>
    </xsl:template>
    
    <xsl:template match="Accounts">
        <ServiceCodes>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </ServiceCodes>
    </xsl:template>
    
    <xsl:template match="Account">
        <ServiceCode>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </ServiceCode>
    </xsl:template>
    
    <xsl:template match="ServiceCode">
        <Code>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </Code>
    </xsl:template>
    
    
</xsl:stylesheet>

Open in new window

0
 

Author Comment

by:cperry-sca
ID: 39220989
Gertone - your solution did not work for me but that's probably because I did something wrong.  I do not work with XML regularly and I certainly don't use XSLTs very often.

I have generated a few source schemas and test xml files that I hope will better identify the issue.

I generated the following files using Altova XMLSpy 2013

Source Schema

<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSpy v2013 rel. 2 (x64) (http://www.altova.com) by Christopher Perry (Southwest Consulting Associates) -->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
	<xs:element name="Accounts">
		<xs:annotation>
			<xs:documentation>Comment describing your root element</xs:documentation>
		</xs:annotation>
		<xs:complexType>
			<xs:sequence>
				<xs:element name="Account" maxOccurs="unbounded">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="AccountNumber"/>
							<xs:element name="ServiceCode"/>
							<xs:element name="StartDate"/>
							<xs:element name="EndDate"/>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>

Open in new window


Destination Schema

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
	<xs:element name="ServiceCodes">
		<xs:annotation>
			<xs:documentation>Comment describing your root element</xs:documentation>
		</xs:annotation>
		<xs:complexType>
			<xs:sequence>
				<xs:element name="ServiceCode" maxOccurs="unbounded">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="AccountNumber"/>
							<xs:element name="Code"/>
							<xs:element name="StartDate"/>
							<xs:element name="EndDate"/>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>

Open in new window


Source Test File

<?xml version="1.0" encoding="UTF-8"?>
<Accounts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Accounts.xsd">
	<Account>
		<AccountNumber>1</AccountNumber>
		<ServiceCode>A</ServiceCode>
		<StartDate>2013-01-01</StartDate>
		<EndDate>2013-01-31</EndDate>
	</Account>
	<Account>
		<AccountNumber>1</AccountNumber>
		<ServiceCode>B</ServiceCode>
		<StartDate>2013-02-01</StartDate>
		<EndDate>2013-02-28</EndDate>
	</Account>
	<Account>
		<AccountNumber>1</AccountNumber>
		<ServiceCode>B</ServiceCode>
		<StartDate>2013-02-01</StartDate>
		<EndDate>2013-02-27</EndDate>
	</Account>
</Accounts>

Open in new window


Example Result File (after XSLT)

<?xml version="1.0" encoding="UTF-8"?>
<ServiceCodes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="ServiceCodes.xsd">
	<ServiceCode>
		<AccountNumber>1</AccountNumber>
		<Code>A</Code>
		<StartDate>2013-01-01</StartDate>
		<EndDate>2013-01-31</EndDate>
	</ServiceCode>
	<ServiceCode>
		<AccountNumber>1</AccountNumber>
		<Code>B PLEASE REVIEW DUE TO DATE MISMATCH</Code>
		<StartDate>2013-02-01</StartDate>
		<EndDate>2013-02-28</EndDate>
	</ServiceCode>
	<ServiceCode>
		<AccountNumber>1</AccountNumber>
		<Code>B PLEASE REVIEW DUE TO DATE MISMATCH</Code>
		<StartDate>2013-02-01</StartDate>
		<EndDate>2013-02-27</EndDate>
	</ServiceCode>
</ServiceCodes>

Open in new window

0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
ID: 39221243
First, it helps when you post an example at the time you post your question, not after I figured it all out

Second, "it does not work" is not helpful. What does not work? If I run my XSLT agaisnt your sample file, it does exactly what I expected... you just seem to need the message at another location, it would have been nice to get at least that confirmation

Any details you can provide help me to not waste time on figuring out

Anyway, here is a new stylesheet. if this is not what you need, I need more detail

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    <xsl:key name="account" match="Account" use="ServiceCode"/>
    
    
    <xsl:template match="ServiceCode">
        <xsl:variable name="this-sdate" select="../StartDate"/>
        <xsl:variable name="this-edate" select="../EndDate"/>
        <Code>
            <xsl:copy-of select="@*"/>
            <xsl:choose>
                <xsl:when test="key('account', .)[not(StartDate = $this-sdate) or not(EndDate = $this-edate) ]">
                    <xsl:value-of select="."/>
                    <xsl:text> PLEASE REVIEW DUE TO DATE MISMATCH</xsl:text>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:apply-templates select="node()"/>
                </xsl:otherwise>
            </xsl:choose>
        </Code>
    </xsl:template>
    
    
    <xsl:template match="node()">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </xsl:copy>
    </xsl:template>
    
    <xsl:template match="Accounts">
        <ServiceCodes>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </ServiceCodes>
    </xsl:template>
    
    <xsl:template match="Account">
        <ServiceCode>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </ServiceCode>
    </xsl:template>
    
     
</xsl:stylesheet>

Open in new window

0
 

Author Closing Comment

by:cperry-sca
ID: 39227115
The solution works well.  I'd give you an "A" but your response was mildly unprofessional.
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39227178
AAA technical solution graded a B because you felt I was mildly unprofessional?
"Thank you" would have worked as well :-)

My apologies if I offended you in any way. That was not my intention.

Please note that we are all volunteers here and that I am trying to help as many people as possible in the little time I got for this
I was just trying to indicate that there are ways that you can make a question clear right from the start, which would save me a lot of time trying to figure out what exactly you need. Time I could then spend helping others. Please try to understand my frustration if I hack a solution together and you seem to need something else...

Anyway, I am sorry if my tone was a bit sharp... I am a volunteer (the only 'professionals' at EE are labeled staff) and my mothertongue is not English. Blame it to that. Try to read beyond the actual words. And please accept my comment by its meaning, it might help you post a more clear question next time

I am glad I could help
Cheers

Geert
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

864 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

23 Experts available now in Live!

Get 1:1 Help Now