How do you remove parent records that don't have any children in a Biztalk Map?

Question

Imagine you have the following source data with a parent-children relationship between a Box and its Cakes.

<Box id = 1>
   <Cakes>
    <Cake id =1>
            <some other tags ...>
    </Cake>
    <Cake id =2>
            <some other tags ...>
    </Cake>
   </Cakes>   
</Box>
<Box id = 2>    
    <Cakes/>   
</Box>

The goal is to properly remove the boxes that contain no cakes (such as box 2) in the destination, while keeping the ability to map the Cake elements and their children tags properly in the graphical mapper (so without using a raw XSLT expression that would prevent that).

Is there a nice way to do this using functoids? Or something else?

score 2 · Answer 1 · edited May 23 '17 at 12:26

I'm a bit one dimensional when it comes to mapping, but we invariably use the mapper to prototype our XSLT and then switch the map to using a Custom XSLT stylesheet

See these SO posts Here and here on how to convert the map to XSLT

Your XSLT will look something like

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:msxsl="urn:schemas-microsoft-com:xslt"
        xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var"
        exclude-result-prefixes="msxsl var userCSharp"
        version="1.0"
        xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp">

    <xsl:output
        omit-xml-declaration="no"
        method="xml"
        version="1.0"
        encoding="UTF-8" />

    <xsl:template match="/">
        <xsl:apply-templates select="Boxes" />
    </xsl:template>
    <xsl:template match="Boxes">
        <MappedBoxes>
            <xsl:for-each select="Box[count(Cakes/Cake) > 0]">
                <MappedBox>
                    <MappedCakes>
                        <xsl:for-each select="Cakes/Cake">
                            <MappedCake>
                                <xsl:value-of select="Tag/text()" />
                            </MappedCake>
                        </xsl:for-each>
                    </MappedCakes>
                </MappedBox>
            </xsl:for-each>
        </MappedBoxes>
    </xsl:template>
</xsl:stylesheet>

This was the XML I used

<Boxes>
    <Box id = "1">
        <Cakes>
            <Cake id ="1">
                <Tag>FirstCake</Tag>
            </Cake>
            <Cake id ="2">
                <Tag>SecondCake</Tag>
            </Cake>
        </Cakes>
    </Box>
    <Box id = "2">
        <Cakes/>
    </Box>
    <Box id = "3">
    </Box>
    <Box id = "4">
        <Cakes>
            <Cake id ="4">
                <Tag>FourthCake</Tag>
            </Cake>
            <Cake id ="5">
                <Tag>FifthCake</Tag>
            </Cake>
        </Cakes>
    </Box>
</Boxes>

Thanks but I have a lot of visual mapping done (the part is actually very complex and deep, and already all done with the visual mapper and tested). So it won't do for that case to use a custom XSLT like this. I would need a solution using the capabilities of the visual mapper of biztalk. — TheOwlsAreNotWhatTheySeem, Jan 24 '11 at 08:26
View the source of your *.btm.cs files and see what VS does with your visual maps ;) — StuartLC, Jan 24 '11 at 08:35
I get the idea but that kinda defeats the purpose of using Biztalk doesn't it? If I just want to write custom XSLT I can do that in my .NET apps directly ... and that solution needs to be readable / maintainable to someone who isn't that deeply technical. — TheOwlsAreNotWhatTheySeem, Jan 24 '11 at 09:56

score 2 · Accepted Answer · answered Jan 24 '11 at 13:36

You can test if the id attribute of the first Cake child of the current Box returns an empty string.

You get the desired transformation using the following functoids:

1) insert Iteration functoid and connect to Box node

2) insert Index functoid and connect to id attribute of Cake node

3) connect Index functoid to Iteration functoid (output side)

4) insert Not Equal functoid and connect to Index functoid (output side)

5) configure Not Equal functiod input; add empty string constant

6) connect Not Equal output to target message Box node

7) insert Value Mapping functoid

8) connect Not Equal output to Value Mapping input side

9) connect id attribute of source message Box node to Value Mapping input

10) connect Value Mapping output to id attribute of target message Box node

Sorry I can't provide a screenshot right now; I hope you can figure out from my description.

How do you remove parent records that don't have any children in a Biztalk Map?

2 Answers2