Split logic when a field contains multiple value

[gabe_f]

Hello,

I have requirement where I need to split the output value when the input field contains multiple value . This could maybe achieved using a filter, splitter, and grouping the result back, but was hoping to find a simpler solution.

Thank you in advance for any recommendation or suggestions.

Input file

<File>

<Number>1</Number>

<Location>A1</Location>

</File>

<File>

<Number>2,3,4</Number>

<Location>B11</Location>

</File>

Target Output file

<File>

<Number>1</Number>

<Location>A1</Location>

</File>

<File>

<Number>2</Number>

<Location>B11</Location>

</File>

<File>

<Number>3</Number>

<Location>B11</Location>

</File>

[MortenWittrock · Accepted solution]

Hi Gabriel

You tagged a couple of different products there, but I'm going to assume that you are working in SAP Cloud Platform Integration.

You can achieve what you want via mapping. As you probably know, there are three mapping options in CPI: Message mapping (i.e. graphical mapping), Groovy/JavaScript mapping and XSLT. You could write this mapping using all three, but the simplest solution would probably be done in either scripting or XSLT.

I'm partial to XSLT :-), so I've written a small XSLT stylesheet, which performs the mapping you need. It looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="3.0">
    <xsl:template match="/">
        <Output>
            <xsl:for-each select="//File">
                <xsl:variable name="loc" select="string(Location)"/>
                <xsl:for-each select="tokenize(string(Number), ',')">
                    <File>
                        <Number><xsl:value-of select="."/></Number>
                        <Location><xsl:value-of select="$loc"/></Location>
                    </File>
                </xsl:for-each>
            </xsl:for-each>
        </Output>
    </xsl:template>
</xsl:stylesheet>

When I pass it this input:

<?xml version="1.0" encoding="UTF-8"?>
<Input>
    <File>
        <Number>1</Number>
        <Location>A1</Location>
    </File>
    <File>
        <Number>2,3,4</Number>
        <Location>B11</Location>
    </File>
</Input>

I get the following output:

<?xml version="1.0" encoding="UTF-8"?>
<Output>
    <File>
        <Number>1</Number>
        <Location>A1</Location>
    </File>
    <File>
        <Number>2</Number>
        <Location>B11</Location>
    </File>
    <File>
        <Number>3</Number>
        <Location>B11</Location>
    </File>
    <File>
        <Number>4</Number>
        <Location>B11</Location>
    </File>
</Output>

You didn't specify the name of the root output element, so I just chose "Output", but you can change that easily, of course.

Have fun,

Morten

[gabe_f · Accepted solution]

Exactly what I was looking for. Thank you, Morten.

[vijay3773 · Accepted solution]

Dear Morten

Good Evening!

Thanks for the insight, i was trying exaclty the same but unable to get the desired output.

I'm working on a similar requirement to form the xml output as per the below.

Can you please advise.

INPUT XML:

<?xml version="1.0" encoding="UTF-8"?> <JobApplication> <JobApplication> <Header> <jobReqID>Job Req ID</jobReqID> <question>Pre-Screening Question</question> <answer>Pre- Screening Answer</answer> </Header> <Record> <jobReqID>13295</jobReqID> <question>Question1, Question2, Question3, Question4, Question5</question> <answer>YAnswer1, Answer2, Answer3, Answer4, Answer5</answer> </Record> <Record> <jobReqID>13296</jobReqID> <question>Question1, Question2, Question3, Question4, Question5</question> <answer>YAnswer1, Answer2, Answer3, Answer4, Answer5</answer> </Record> </JobApplication> </JobApplication>

Expected Output:

<?xml version="1.0" encoding="UTF-8"?> <JobApplication> <JobApplication> <Header> <jobReqID>Job Req ID</jobReqID> <question>Pre-Screening Question</question> <answer>Pre- Screening Answer</answer> </Header> <Record> <jobReqID>13295</jobReqID> <question>Question1</question> <answer>Answer1</answer> </Record> <Record> <jobReqID>13295</jobReqID> <question>Question2</question> <answer>Answer2</answer> </Record> <Record> <jobReqID>13295</jobReqID> <question>Question3</question> <answer>Answer3</answer> </Record> <Record> <jobReqID>13295</jobReqID> <question>Question4</question> <answer>Answer4</answer> </Record> <Record> <jobReqID>13295</jobReqID> <question>Question5</question> <answer>Answer5</answer> </Record> <Record> <jobReqID>13296</jobReqID> <question>Question1</question> <answer>Answer1</answer> </Record> <Record> <jobReqID>13296</jobReqID> <question>Question2</question> <answer>Answer2</answer> </Record> <Record> <jobReqID>13296</jobReqID> <question>Question3</question> <answer>Answer3</answer> </Record> <Record> <jobReqID>13296</jobReqID> <question>Question4</question> <answer>Answer4</answer> </Record> <Record> <jobReqID>13296</jobReqID> <question>Question5</question> <answer>Answer5</answer> </Record> </JobApplication> </JobApplication>

XSLT Code:

<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="3.0"> <xsl:template match="/"> <JobApplication> <JobApplication> <xsl:for-each select="//Record"> <xsl:variable name="reqID" select="string(jobReqID)"/> <xsl:for-each select="tokenize(string(Number), ',')"> <Record> <answer><xsl:value-of select="."/></answer> <question><xsl:value-of select="."/></question> <jobReqID><xsl:value-of select="$reqID"/></jobReqID> </Record> </xsl:for-each> </xsl:for-each> </JobApplication> </JobApplication> </xsl:template> </xsl:stylesheet>

[MortenWittrock · Accepted solution]

Hi vijay3773

Please post this as a new question. Keep in mind to use the "Code" button to insert your XML snippets.

Regards,

Morten

[vijay3773 · Accepted solution]

Sorry Some how the link has been corrupted Apologies Morten.

https://answers.sap.com/questions/13340410/xlst-mapping-query-sap-cpi.html

here is the post where the query was raised,can you please have a look and advise/suggest.