|
Home > Archive > BizTalk Server Orchestration > October 2004 > SQL Multiple Results Sets
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Multiple Results Sets
|
|
| Neal Walters 2004-10-07, 5:47 pm |
| In ADO.NET we can call a Stored Procedure that returns multiple results sets.
Can we call this type fo stored procedure from Biztalk Sql Adapter (in an
Orchestration)? We were trying to do this, because apparently "FOR XML
EXPLICIT" is not supported. What we need is to return data from two (or
more) tables that don't necessarily have any relationship to each other. We
would like to do it in one SQL.
Neal Walters
http://Biztalk-Training.com - FREE Biztalk Demos and Videos
| |
| Alan Smith 2004-10-08, 2:46 am |
| HI Neal,
I got this to work in a prototype, and it seams to work OK.
/Alan
"Neal Walters" wrote:
> In ADO.NET we can call a Stored Procedure that returns multiple results sets.
> Can we call this type fo stored procedure from Biztalk Sql Adapter (in an
> Orchestration)? We were trying to do this, because apparently "FOR XML
> EXPLICIT" is not supported. What we need is to return data from two (or
> more) tables that don't necessarily have any relationship to each other. We
> would like to do it in one SQL.
>
> Neal Walters
> http://Biztalk-Training.com - FREE Biztalk Demos and Videos
>
| |
| Neal Walters 2004-10-08, 7:46 am |
| Hi Alan,
Your "The Bloggers Guide to BizTalk " is great. You did a lot more than
I expected and I hope to contribute soon.
So, in your prototype to handle the two results sets - what did you do?
The SQL adapter seems to build the SQLService.xsd for only the first SQL
statement in the stored procedure. I thought about building a second element
with fields for the second SQL statement, but I didn't want to waste 30
minutes if I knew it would not work. Was this your approach?
So for example, suppose I want to retrieve all authors and all titles
from the "pubs" demo database. The schema that the SQL Adapter built looks
like this (I'm giving just the structure without closing tags or without full
xsd)
<Schema>
<SQLTestReq>
<TwoResults> <!-- this is the stored proc name -->
<SQLTestResp>
<authors>
<au_id>
<au_lname>
Would I just change it to this:
<Schema>
<SQLTestReq>
<TwoResults> <!-- this is the stored proc name -->
<SQLTestResp>
<authors>
<au_id>
<au_lname>
<titles>
<title_id>
<title>
Thanks again!
Neal Walters
http://Biztalk-Training.com - Free Biztalk 2004 Demos
| |
| Alan Smith 2004-10-08, 5:48 pm |
| Hi,
More content for the guide would be great!
My SP looks like this:
ALTER PROCEDURE SelectTwoTables
AS
select * from Customer for xml auto, --xmldata
select * from Supplyer for xml auto, --xmldata
The response part of the schema looks like this:
- <xs:element name="TwoTableTestResponse">
- <xs:complexType>
- <xs:sequence>
<xs:element xmlns:q1="http://TwoTableTest" minOccurs="0"
maxOccurs="unbounded" name="Customer" type="CustomerType" />
<xs:element minOccurs="0" maxOccurs="unbounded" name="Supplyer"
type="SupplyerType" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:complexType name="CustomerType">
<xs:attribute name="CustomerID" type="xs:int" />
<xs:attribute name="SupplyerID" type="xs:int" />
<xs:attribute name="CustomerName" type="xs:string" />
</xs:complexType>
- <xs:complexType name="SupplyerType">
<xs:attribute name="SupplyerID" type="xs:int" />
<xs:attribute name="SupplyerName" type="xs:string" />
</xs:complexType>
I had to add the schema infor for second result set manually, but it's not
too hard to do.
This is a real basic prototype, so I'm not sure how good it works in
production.
Good luck with it,
/Alan
"Neal Walters" wrote:
> Hi Alan,
>
> Your "The Bloggers Guide to BizTalk " is great. You did a lot more than
> I expected and I hope to contribute soon.
>
> So, in your prototype to handle the two results sets - what did you do?
> The SQL adapter seems to build the SQLService.xsd for only the first SQL
> statement in the stored procedure. I thought about building a second element
> with fields for the second SQL statement, but I didn't want to waste 30
> minutes if I knew it would not work. Was this your approach?
>
> So for example, suppose I want to retrieve all authors and all titles
> from the "pubs" demo database. The schema that the SQL Adapter built looks
> like this (I'm giving just the structure without closing tags or without full
> xsd)
>
> <Schema>
> <SQLTestReq>
> <TwoResults> <!-- this is the stored proc name -->
> <SQLTestResp>
> <authors>
> <au_id>
> <au_lname>
>
> Would I just change it to this:
> <Schema>
> <SQLTestReq>
> <TwoResults> <!-- this is the stored proc name -->
> <SQLTestResp>
> <authors>
> <au_id>
> <au_lname>
> <titles>
> <title_id>
> <title>
>
> Thanks again!
>
> Neal Walters
> http://Biztalk-Training.com - Free Biztalk 2004 Demos
>
| |
|
|
|
|
|