sql server - BizTalk WCF SQL Adapter: How to receive records from a database with one-to-many relationships -

i have seen many tutorials going on how use wcf sql adapter in biztalk receive port pull in data sql server database. however, have been unable find resources on how best handle same kind of operation when data working has one-to-many relationship.

for example, have database 3 tables: team, player , sponsor. team table in one-to-many table player , sponsor tables. basically, team can have many players, player can belong 1 team. likewise, team can have multiple sponsors, sponsor support 1 team.

i want biztalk application poll new team records along related data. when new team added, want use stored procedure pull in team players , sponsors team. xsd resulting xml of course allow multiple player , sponsor records.

i technically use xml path assemble entire xml structure within stored procedure , return biztalk application, approach result in unnecessarily complicated stored procedure. (i'm not working such small database structure. example sake of simplicity.)

this brings me actual question: best practices retrieving records in one-to-many relationship database construct fully-realized xml message can use in biztalk application?

is there way this using stored procedure , wcf sql adapter? solution have been able come use separate stored procedure each table, use map or orchestration assemble various pieces canonical schema. maybe indeed best approach, know if there simple missing.

your stored procedure should this.

select [teamname]  [team] [teamid] = @newteamid  select [playername] [player] [teamid] = @newteamid   select [sponsorname]  [sponsor] [teamid] = @newteamid  

then generate schema via consume adapter service. schema 3 record sets can map nicer schema.