BizTalk XML mapping query

jabsys posted on Wed, Jun 10 2009 4:54 AM

rated by 0 users

Hello. I've got a problem whereby I'm attempting to map a well-formed, but not particularly useful, XML file into another more useful one.

Example source XML:

<table>
  <row>
    <column>101000041468</column>
    <column>Environment - Graffiti Incident</column>
    <column>Mr Joe Bloggs</column>
    <column>Environment</column>
    <column>Street Cleansing</column>
    <column>Graffiti</column>
    <column>1</column>
    <column>12 May 09</column>
  </row>
  <row>...</row>
  <row>...</row>
</table>

Appears in BizTalk schema designer as:

table
  row
    column

Example output desired:

<ns0:Root xmlns:ns0="http://graffiti_final">
  <Case>
    <Reference>101000041468</Reference> 
    <Title>Environment - Graffiti Incident</Title> 
    <Associated>Mr Joe Bloggs</Associated> 
    <Subject>Environment</Subject> 
    <Reason>Street Cleansing</Reason> 
    <Type>Graffiti</Type> 
    <Allocation>Graffiti</Allocation> 
    <Status>1</Status> 
    <Created>12 May 09</Created>
  </Case>
  <Case>...</Case>
</ns0:Root>

Appears in schema designer as:

Root
  Case
    Reference
    Title
    Associated
    Subject
    Reason
    Type
    Allocation
    Status
    Created

I've tried using the Index functoid, which I managed to get to work but only for the first instance of row in the source xml.  I couldn't see a way to feed a loop into Index so looked elsewhere.

I've tried Table Loop/Extractor combo and managed to get it to loop through all rows but only output the Reference into each output column.

I'm really not all that familiar with figuring this out (obviously) so if anyone has any suggestions or even references I can refer to to aid me in this, that'd be a great help. 

Thanks in advance.