BizTalk Mapper: Link between siblings

Question

I have the following XML that I need to transform:

<?xml version="1.0" encoding="utf-8"?>
<TestRecords>
  <TestData>
    <Users>
      <User>
        <Id>BG123</Id>
        <Name>Bill Gates</Name>
      </User>
      <User>
    <Id>SN123</Id>
    <Name>Satya Nadella</Name>
  </User>
</Users>
<UserDetails>
  <UserDetail>
    <UserId>SN123</UserId>
    <CompanyName>Microsoft Corp</CompanyName>
  </UserDetail>
  <UserDetail>
    <UserId>
      <UserId>BG123</UserId>
      <CompanyName>Bill Gates Foundation</CompanyName>
    </UserId>
  </UserDetail>
</UserDetails>

I need to map this XML into the following XML:

<?xml version="1.0" encoding="utf-8"?>
<TestRecords>
  <TestData>
    <Users>
      <User>
        <Id>BG123</Id>
        <Name>Bill Gates</Name>
        <CompanyName>Bill Gates Foundation</CompanyName>
      </User>
      <User>
        <Id>SN123</Id>
        <Name>Satya Nadella</Name>
        <CompanyName>Microsoft Corp</CompanyName>
      </User>
    </Users>
  </TestData>
</TestRecords>

When I loop over Users/User, I need to find the UserDetail where UserDetail/UserId is equal to the current User/Id

Thank you and best regards

Michael

It's quite common scenario. You can't do this using standard functiods. The simplest way to achieve this is Custom XSLT Template functiod. Try to google "BizTalk merge/join messages" e.g. https://adventuresinsidethemessagebox.wordpress.com/2012/04/01/merging-details-from-two-messages-using-a-biztalk-map/ — kletnoe, May 20 '14 at 07:40
thanks, I managed to make it work as explained in the blog post — MFox, May 20 '14 at 14:55
@FCR Actually it is possible with standard functoids but sometimes may not be the most efficient way. See my answer below. P.S. The blog post you pointed to is that of a work colleague of mine. — Dijkgraaf, May 22 '14 at 00:23

score 2 · Answer 1 · answered May 22 '14 at 00:20

If you don't want to do Custom XSLT as suggested by FCR the only other option when you have different looping structures is to have an intermediate schema and two maps.

UserIn to UserInt

Which produces

<TestRecords>
    <TestData>
        <Users>
            <User>
                <Id>BG123</Id>
                <Name>Bill Gates</Name>
                <UserDetails>
                    <UserID>SN123</UserID>
                    <CompanyName>Microsoft Corp</CompanyName>
                </UserDetails>
                <UserDetails>
                    <UserID>BG123</UserID>
                    <CompanyName>Bill Gates Foundation</CompanyName>
                </UserDetails>
            </User>
            <User>
                <Id>SN123</Id>
                <Name>Satya Nadella</Name>
                <UserDetails>
                    <UserID>SN123</UserID>
                    <CompanyName>Microsoft Corp</CompanyName>
                </UserDetails>
                <UserDetails>
                    <UserID>BG123</UserID>
                    <CompanyName>Bill Gates Foundation</CompanyName>
                </UserDetails>
            </User>
        </Users>
    </TestData>
</TestRecords>

Which you can then run through this second map to produce the desired outcome.

UserInt to UserOut

This will become very inefficient however if the second list is large.

Yes, I didn't thought about this. A little bit freaky solution, btw =) — kletnoe, May 22 '14 at 09:53

score 1 · Answer 2 · edited May 23 '17 at 11:57

This is a common lookup pattern in xslt and there is also the opportunity to use xsl:key to create an index which can boost performance on large documents. Refer here if you need to convert a .btm to xslt.

(Also, I'm assuming that there isn't a double wrapper UserId at on the last UserDetails/UserDetail element):

<?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:key name="userLookup" 
            match="/TestRecords/TestData/UserDetails/UserDetail" use="UserId"/>

   <!--identity template - copy everything by default --> 
   <xsl:template match="@* | node()">
      <xsl:copy>
         <xsl:apply-templates select="@* | node()"/>
      </xsl:copy>
   </xsl:template>

   <!--i.e.match only Users in the first Users/User tree. Actually the explicit
       ancestor qualifier is redundant because of the other suppress template -->
   <xsl:template match="User[ancestor::Users]">
      <User>
         <xsl:copy-of select="child::*" />
         <CompanyName>
            <xsl:value-of select="key('userLookup', Id)/CompanyName"/>
        </CompanyName>
      </User>   
   </xsl:template>

   <!--Suppress the second userdetails part of the tree entirely -->
   <xsl:template match="UserDetails" />
</xsl:stylesheet>

Fiddle here

BizTalk Mapper: Link between siblings

2 Answers2