BizTalk - Concatenation of repetitive node with output to json array

Question

I have something like below as input xml. As you can see, the node MIF can come multiple times (one or two times).

<Transactions>
    <Events>
        <Properties>
            <ENT_SUB_TYP />
            <MIF_ACT_PER>Not app</MIF_ACT_PER>
        </Properties>
    </Events>
</Transactions>
<Transactions>
    <Events>
        <Properties> 
            <ENT_SUB_TYP />
            <MIF_ACT_PER>3</MIF_ACT_PER>
        </Properties>
    </Events>
</Transactions>
<Transactions>
    <Events>
        <Properties>
            <ENT_SUB_TYP />
            <MIF_ACT_PER>6</MIF_ACT_PER>
        </Properties>
    </Events>
</Transactions>
<Transactions>
    <Events>
        <Properties>
            <ENT_SUB_TYP />
            <MIF_ACT_PER>3</MIF_ACT_PER>
            <MIF_ACT_PER>6</MIF_ACT_PER>
        </Properties>
    </Events>
</Transactions>

The corresponding JSON output file is supposed to look something like below:

{
  "Transactions": [
    {
      "Events": [
        {
          "Properties": [
            {
              "ENT_SUB_TYP": [
                "NOT_APP"
              ]
            },
            {
              "MIF_ACT_PER": [
                "NOT_APP"
              ]
            }
          ]
        }
      ]
    },
    {
      "Events": [
        {
          "Properties": [
            
            {
              "ENT_SUB_TYP": [
                "NOT_APP"
              ]
            },
            {
              "MIF_ACT_PER": [
                "3"
              ]
            }
          ]
        }
      ]
    },
    {
      "Events": [
        {
          "Properties": [
            
            {
              "ENT_SUB_TYP": [
                "NOT_APP"
              ]
            },
            {
              "MIF_ACT_PER": [
                "6"
              ]
            }
          ]
        }
      ]
    },
    {
      "Events": [
        {
          "Properties": [
            
            {
              "ENT_SUB_TYP": [
                "NOT_APP"
              ]
            },
            {
              "MIF_ACT_PER": [
                "3",
                "6"
              ]
            }
          ]
        }
      ]
    }
  ]
}

Now, I do think that the output schema (showing only the interesting part) is supposed to become something like below but I am only guessing. I dont know how the regular BizTalk JSON translator interprets the output schema.

<Properties>
    <ENT_SUB_TYP>NOT_APP</ENT_SUB_TYP>
</Properties>
<Properties>    
    <MIF_ACT_PER>3</MIF_ACT_PER>
    <MIF_ACT_PER>6</MIF_ACT_PER>
</Properties>

How can I do this in the mapper? No matter how I try I just cant figure out how to use the functoids. And I dont understand XSLT so that goes out of the window as well (at least for me. If anyone understands how to, ie. use xslt and and the scripting functoid, that would be good as well). Any help to solve this issue is greatly appreciated!

Update: This picture represents the faulty output which gives me the '3' and '6' in their own properties node whereas, as stated before, I would like to have them in in one properties node.

Update 2: According to Mr. Dijkgraaf's suggestion I am updating the post. Hopefully this time it will be clear enough to understand my issue and also my confusion.

The four example input below are barebones of the real input examples I use during development. Data has been masked.

<?xml version="1.0" encoding="utf-8"?>
<ns0:Masterdata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns0="http://Masterdata/">
  <EntityType xmlns="">CRD</EntityType>
  <Transactions xmlns="">
    <EntityType>CRD</EntityType>
    <Events>
      <Properties>
        <ENT_SUB_TYP>NOT_APP</ENT_SUB_TYP>
        <MIF_ACT_PER>NOT_APP</MIF_ACT_PER>
      </Properties>
    </Events>
  </Transactions>
</ns0:Masterdata>


<?xml version="1.0" encoding="utf-8"?>
<ns0:Masterdata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns0="http://Masterdata/">
  <EntityType xmlns="">CRD</EntityType>
  <Transactions xmlns="">
    <EntityType>CRD</EntityType>
    <Events>
      <Properties>
        <ENT_SUB_TYP>NOT_APP</ENT_SUB_TYP>
        <MIF_ACT_PER>3</MIF_ACT_PER>
      </Properties>
    </Events>
  </Transactions>
</ns0:Masterdata>


<?xml version="1.0" encoding="utf-8"?>
<ns0:Masterdata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns0="http://Masterdata/">
  <EntityType xmlns="">CRD</EntityType>
  <Transactions xmlns="">
    <EntityType>CRD</EntityType>
    <Events>
      <Properties>
        <ENT_SUB_TYP>NOT_APP</ENT_SUB_TYP>
        <MIF_ACT_PER>6</MIF_ACT_PER>
      </Properties>
    </Events>
  </Transactions>
</ns0:Masterdata>


<?xml version="1.0" encoding="utf-8"?>
<ns0:Masterdata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns0="http://Masterdata/">
  <EntityType xmlns="">CRD</EntityType>
  <Transactions xmlns="">
    <EntityType>CRD</EntityType>
    <Events>
      <Properties>
        <ENT_SUB_TYP>NOT_APP</ENT_SUB_TYP>
        <MIF_ACT_PER>3</MIF_ACT_PER>
        <MIF_ACT_PER>6</MIF_ACT_PER>
      </Properties>
    </Events>
  </Transactions>
</ns0:Masterdata>

Now, the output of the mapping using the above example with the '4' and '6' gives me below:

<ns0:Masterdata xmlns:ns0="http://Masterdata">
    <Transactions>
        <EntityType>CRD</EntityType>
        <Events>            
            <Properties>
                <ENT_SUB_TYP>NOT_APP</ENT_SUB_TYP>
            </Properties>
            <Properties>
                <MIF_ACT_PER>3</MIF_ACT_PER>
            </Properties>
            <Properties>
                <MIF_ACT_PER>6</MIF_ACT_PER>
            </Properties>
        </Events>
    </Transactions>
</ns0:Masterdata>

The mapping is exactly as the picture in the first update shows. And that mapping gives me, if I use the last example file that contains both the '4' and '6', the JSON below.

{
  "Transactions": [
    {
      "EntityType": "CRD",
      "Events": [
        {
          "Properties": [            
            {
              "ENT_SUB_TYP": [
                "NOT_APP"
              ]
            },
            {
              "MIF_ACT_PER": [
                "3"
              ]
            },
            {
              "MIF_ACT_PER": [
                "6"
              ]
            }
          ]
        }
      ]
    }
  ]
}

Now, regarding the source schema and target schema in the mapping, this is what I did. The source schema worked as a blueprint for the target schema. As you can see, the target schema in the mapping does contain a choice node. So this means that nodes that have data in them (in other words do fulfill the logical string functoid requirement) will be sent to the target and output xml.

Regarding the source and target schema, I really wish that I could share them with you. But a confidentialty clause prevents me from doing that.

I think I could perhaps create a dummy schema but then I am not sure that I would actually be able to implement your suggested solution to the real schema. There really isn't anything particular at all with the schemas except for the choice node in the target schema. I really hope this will be enough.

I have tried to follow Mr. Dijkgraafs solution as to make the target schema as simple as possible but there really is nothing to simplify. (Yes, I am the first to admit that I am far from an expert but still, I do have a few years of experience.)

What can be said about the mapping then? The target system requires every property in the source schema to come as a child node of a properties node. It is easier to show it than to explain it. See picture below.

I am doing something wrong regarding the looping functoid (which results in the 4 and 6 coming in their own Properties node instead of them both coming in one).

I can also tell that my value mapping functoid looks like this:

Finally, I hope this will be enough but I am open to suggestions if anyone needs more input. And also, thanks a lot for your patience with me and my incomplete descriptions.

Answer 1

No concatenation needed, you just need to define your schema correctly and the JSON Encoder will put the repeating elements into arrays.

First of all your sample input XML doesn't have a root node, so I'm going to add one as well as a namespace, as without it the JSON Encoder does not use the schema definition and instead gives slightly inconsistent results. If your input doesn't have a namespace, you can have one schema to parse the input, and a second schema that you map to that does have the namespace, or have a Pipeline component add the namespace.

Input

<ns0:Transaction xmlns:ns0="https://SO72306470">
    <Transactions>
        <Events>
            <Properties>
                <ENT_SUB_TYP />
                <MIF_ACT_PER>Not app</MIF_ACT_PER>
            </Properties>
        </Events>
    </Transactions>
    <Transactions>
        <Events>
            <Properties> 
                <ENT_SUB_TYP />
                <MIF_ACT_PER>3</MIF_ACT_PER>
            </Properties>
        </Events>
    </Transactions>
    <Transactions>
        <Events>
            <Properties>
                <ENT_SUB_TYP />
                <MIF_ACT_PER>6</MIF_ACT_PER>
            </Properties>
        </Events>
    </Transactions>
    <Transactions>
        <Events>
            <Properties>
                <ENT_SUB_TYP />
                <MIF_ACT_PER>3</MIF_ACT_PER>
                <MIF_ACT_PER>6</MIF_ACT_PER>
            </Properties>
        </Events>
    </Transactions>
</ns0:Transaction>

Schema that matches this.

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://Scratch2.SO72306470_JSON" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://Scratch2.SO72306470_JSON" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Transactions">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Events">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Properties">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element minOccurs="0" maxOccurs="unbounded" name="ENT_SUB_TYP" type="xs:string" />
                    <xs:element minOccurs="0" maxOccurs="unbounded" name="MIF_ACT_PER" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Then it is a fairly simple map where map back to the same schema and you just need a bit of logic to set the ENT_SUB_TYPE when it is empty.

And a Send Pipeline with the JSON Encoder, and where you set the RemoveOuterEnvelope to True

Drop through the file and the output is.

{
  "Transactions": [
    {
      "Events": [
        {
          "Properties": {
            "ENT_SUB_TYP": [
              "NOT_APP"
            ],
            "MIF_ACT_PER": [
              "Not app"
            ]
          }
        }
      ]
    },
    {
      "Events": [
        {
          "Properties": {
            "ENT_SUB_TYP": [
              "NOT_APP"
            ],
            "MIF_ACT_PER": [
              "3"
            ]
          }
        }
      ]
    },
    {
      "Events": [
        {
          "Properties": {
            "ENT_SUB_TYP": [
              "NOT_APP"
            ],
            "MIF_ACT_PER": [
              "6"
            ]
          }
        }
      ]
    },
    {
      "Events": [
        {
          "Properties": {
            "ENT_SUB_TYP": [
              "NOT_APP"
            ],
            "MIF_ACT_PER": [
              "3",
              "6"
            ]
          }
        }
      ]
    }
  ]
}