Solved

XSLT 1.0 grouping

Posted on 2013-06-04
5
309 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
The solution works well.  I'd give you an "A" but your response was mildly unprofessional.
0
 
LVL 60

Expert Comment

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

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

8 Experts available now in Live!

Get 1:1 Help Now