1

I have seen many tutorials going over how to use the WCF SQL adapter in a BizTalk receive port to pull in data from a SQL Server database. However, I have been unable to find any resources on how best to handle this same kind of operation when the data you are working with has a one-to-many relationship.

For example, say I have a database with three tables: Team, Player and Sponsor. The Team table is in a one-to-many table with the Player and Sponsor tables. Basically, a Team can have many Players, but a Player can only belong to one Team. Likewise, a Team can have multiple Sponsors, but a Sponsor will only support one Team.

I want my BizTalk application to poll for new Team records along with any related data. When a new Team is added, I want to use a stored procedure to pull in that Team as well as all Players and Sponsors for that Team. The XSD for the resulting XML will of course allow for multiple Player and Sponsor records.

I could technically use FOR XML PATH to assemble the entire XML structure from within the stored procedure and return that to the BizTalk application, but that approach would result in an unnecessarily complicated stored procedure. (I'm not really working with such a small database structure. That was just an example for the sake of simplicity.)

This brings me to my actual question: What are some best practices for retrieving records in a one-to-many relationship from a database to construct a fully-realized XML message that I can use in my BizTalk application?

Is there a way do this this just using a stored procedure and the WCF SQL adapter? The only solution I have been able to come up with is to use a separate stored procedure for each table, then use a Map or Orchestration to assemble the various pieces into my canonical schema. Maybe this is indeed the best approach, but I would very much like to know if there is something very simple I am missing.

NspectorHector
  • 143
  • 1
  • 8
  • I haven't worked with BizTalk yet, so this might be rubbish... As you seem to be interested in the full tree always I'd suggest to create a `VIEW` (or even better: an `inline function with parameters`) where you use `INNER JOIN`s to bind your tables and return one flat set. it should be easy to do the rest on application level... – Shnugo Jun 07 '16 at 06:31
  • *Very important questions: 1. Do you want to retrieve one top level records one at a time, like just a single Team, or a batch, like many Teams. 2. How far down does the 1 to many hierarchy go. 1 level would be Team->Players. 2 Levels would be Team->Players->Goals. – Johns-305 Jun 07 '16 at 11:57
  • 1. I am not really tied down to either approach, but since each record will be handled separately I am leaning towards retrieving them one at a time instead of batching if possible. If batching and debatching turns out to be easier, I can certainly be swayed towards that approach. 2. I will actually be working on two separate but similar applications. In one, the one to many will be 1 level deep. In the other, it will be 2 levels deep. I am starting with the 1 level application and will move on to the other one when it is finished. – NspectorHector Jun 07 '16 at 16:53

1 Answers1

3

Your stored procedure should look something like this.

SELECT [TeamName] 
FROM [Team]
WHERE [TeamID] = @NewTeamID

SELECT [PlayerName]
FROM [Player]
WHERE [TeamID] = @NewTeamID 

SELECT [SponsorName] 
FROM [Sponsor]
WHERE [TeamID] = @NewTeamID

Then generate the schema from that via Consume Adapter Service. You will get a schema with three record sets which you can then map to a nicer schema.

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
  • Awesome, thanks Dijkgraaf! After some trial and error, I was able to get it to work. Here are some things to keep in mind for anyone else getting errors. One thing that I didn't pay close enough attention to was that I had some joins containing columns with the same name. As you might have guessed, I had to refactor my query a bit to avoid this. The other thing was that I had temporary tables in my query as well, which caused other issues. For more information on that, refer to this page: http://thoughtsofmarcus.blogspot.com/2010/11/calling-stored-procedures-from-biztalk.html – NspectorHector Jun 08 '16 at 21:23