Search code examples
xmlxsltdictionarybiztalkbiztalk-2013

Biztalk: Grouping Items during mapping

Is there a way to take an XML file that looks like this:

<?xml version="1.0" encoding="utf-8"?>
<spMyStoredProc xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
<StoredProcedureResultSet0>
     <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo/spMyStoredProc">
       <Item>1</Item>
       <Property>something</property>
     </StoredProcedureResultSet0>
     <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo/spMyStoredProc">
       <Item>2</Item>
       <Property>something</property>
     </StoredProcedureResultSet0>
     <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo/spMyStoredProc">
       <Item>3</Item>
       <Property>something</property>
       <Group>1</Group>
     </StoredProcedureResultSet0>
     <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo/spMyStoredProc">
       <Item>4</Item>
       <Property>something</property>
       <Group>1</Group>
     </StoredProcedureResultSet0>
     <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo/spMyStoredProc">
       <Item>5</Item>
       <Property>something</property>
       <Group>2</Group>
     </StoredProcedureResultSet0>
     <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo/spMyStoredProc">
       <Item>6</Item>
       <Property>something</property>
       <Group>2</Group>
     </StoredProcedureResultSet0>
  </StoredProcedureResultSet0>
  <ReturnValue>0</ReturnValue>
</spMyStoredProc>

Come out in this format:

<MyRequests>
    <Request>
       <Item ID="1" Property="Something" />
    </Request>
    <Request>
       <Item ID="2" Property="Something" />
    </Request>
    <Request GroupID="1">
       <Item ID="3" Property="Something" />
       <Item ID="4" Property="Something" />
    </Request>
    <Request GroupID="2">
       <Item ID="5" Property="Something" />
       <Item ID="6" Property="Something" />
    </Request>
</MyRequests>

I'm close with just using a straight map from the source schema to the destination, but it doesn't group the items together that have the same group id in it.

Basically, what i am able to get with no functoids is this:

<MyRequests>
    <Request>
       <Item ID="1" Property="Something" />
    </Request>
    <Request>
       <Item ID="2" Property="Something" />
    </Request>
    <Request GroupID="1">
       <Item ID="3" Property="Something" />
    </Request>
    <Request GroupID="1">
       <Item ID="4" Property="Something" />
    </Request>
    <Request GroupID="2">
       <Item ID="5" Property="Something" />
    </Request>
    <Request GroupID="2">
       <Item ID="6" Property="Something" />
    </Request>
</MyRequests>
Solution

  • It sounds like XSLT 2.0 isn't supported in Biztalk, so this answer is in regard to XSLT 1.0.

    Assuming your XML input file is well-formed, i.e. wrapped in a single top-level element...

    The standard approach for this kind of grouping problem is Meunchian grouping. See for example https://stackoverflow.com/a/1929273/423105 or https://stackoverflow.com/a/2334224/423105. If you have trouble applying those answers, leave a comment with specific questions.