All geek questions in one place

BizTalk WCF SQL Adapter: how to retrieve records from a one-to-many database

I have seen many tutorials on using the WCF SQL adapter in the BizTalk receive port to output data from a SQL Server database. However, I could not find any resources on how to best deal with the same operation when the data you are working with is one to many .

For example, let's say I have a database with three tables: Team, Player and Sponsor. The Team table is in the one-to-many table with the Player and Sponsor tables. In principle, a team can have many players, but a player can only belong to one team. Similarly, a team may have several Sponsors, but a Sponsor will support only one team.

I want the BizTalk application to poll new Team entries along with any related data. When a new team is added, I want to use the stored procedure to pull out this team, as well as all the players and sponsors for this team. The XSD for the resulting XML, of course, will allow the use of multiple entries for players and sponsors.

I can technically use the FOR XML PATH to assemble the entire XML structure from the stored procedure and return it to the BizTalk application, but this approach will lead to an overly complex stored procedure. (I do not work with such a small database structure. This is just an example for simplicity.)

This brings me to my actual question: what are some guidelines for extracting one-to-many records from a database to build a fully implemented XML message that I can use in my BizTalk application?

, WCF SQL? , , - , . , , , - , .

+1
1

.

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

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

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

. , .

+3

Source: https://habr.com/ru/post/1653488/

More articles:


All Articles