Using classic T-SQL if we wanted to get all this data out we could join the two tables and then we would get a longer flat record that
repeated the data for the order in every record as shown below.
With FOR XML we can leverage SQL Server's built in heuristics that will create an XML structure for us that better represents the parent child relationship that is an order and line
items. This query turns out to be very easy as seen in the following query.
* from [Order]
JOIN OrderLine ON Id
= OrderId FOR XML
The resulting XML is also very simple as shown below.
>123 Fake St</
After this everything works exactly as you would expect and we can continue using a pattern that has been well developed and tested
Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is
BizTalk Server Resources on the TechNet Wiki
Great article, good work Dan!
Where is the use of WCF-SQL with XML?