Biztalk: Grouping Items during mapping

Question

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>

The input you showed is not an XML document but an XML fragment, so XSLT wouldn't be able to parse it. It needs to be wrapped in a single top-level element. — LarsH, Jan 27 '14 at 20:54
P.S. Are you using XSLT 1.0 or 2.0? The answer for grouping problems is usually much easier in 2.0. — LarsH, Jan 27 '14 at 20:56
Updated the original XML to show a closer representation of the original document. Only posted fragment earlier due to my company's "sanitization" rules. — SpaceCowboy74, Jan 27 '14 at 21:32

score 1 · Accepted Answer · edited May 23 '17 at 12:11

1

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.

edited May 23 '17 at 12:11

Community

1
1

answered Jan 27 '14 at 21:04

LarsH

27,481
8
94
152

Thanks! I'll give that a look over and see what i can come up with. – SpaceCowboy74 Jan 27 '14 at 21:32
1

@OP: For another good explanation of Muenchian grouping, see http://www.jenitennison.com/xslt/grouping/muenchian.html – LarsH Jan 27 '14 at 22:00
I think i got the grouping to work for the most part, but it only returns the integer values. Is there anyway to accept null or missing as a valid value? – SpaceCowboy74 Jan 28 '14 at 19:40
@OP: please post your code, input, and current output. You can either edit your question to do this, or post a new question. – LarsH Jan 28 '14 at 20:13

Biztalk: Grouping Items during mapping

1 Answers1