|
Home > Archive > BizTalk Server Orchestration > May 2004 > Is this possible in BizTalk 2004
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 |
Is this possible in BizTalk 2004
|
|
|
| Currently we have a .NET application that calls one stored procedure and gets back 5 tables from our SQL DB. We then build an XML file in our .Net application based on the returned data. This XML is then dropped in a directory for BizTalk to pick up and
map to our customers XSD requirements, once mapped we then FTP the data to them.
What we would like to do if at all possible is have BizTalk call this stored procedure, build the XML based on our schema, map the data and send it to our customer. So basically everything will be handled in BizTalk as opposed to our current solution whe
re part of it is handled in our .Net application and part in BizTalk.
My concern is that the data returned from our stored proc is variable, like most stored procs, so how can we be assured that when we first create our SQL adapter generated schema based on the stored proc that we are calling, that schema will always remain
the same…. Does this make sense? Any help would be greatly appreciated!
Thanks,
Jim
| |
| Ehab Sobhy 2004-05-18, 5:47 pm |
| It depends on the definition of "the data returned from our stored proc is
variable", if there is a IF statment for ex that will either return data
from table 1 or table 2 with totally different schemas, then the answers
is no, if the data returning varies in size (multible rows or so) it is
handled by the XML schema generated.
But the good news is that you can always play with your stored procedure to
return some fixed schema , or if you dont want to change the stored
procedures, you can build a simple one on top of them that will unify /
uniion the resultset schema . For ex, if some column sometimes is there and
sometimes not, you can always make sure it is returned from the stored
procedure , even if it is null.
Just remember to use For XML AUTO, XMLDATA in your procedure when
generating the XML schema from BizTalk and of course remove the XMLDATA at
runtime.
| |
|
| Thanks for the reply Ehab. What you have said makes a lot of sense. Yes, by variable I meant that we do have certain conditions that produce a different table structure over another if certain conditions are met/omitted. I can see where this is difficu
lt to handle because the original schema generated by XMLDATA could be different if certain conditions occur. Because our stored proc is very complicated and contains numerous aggregates which FOR XML does not allow (Although this could be worked around
using temp tables), and because we would have to do some very complicated structure building using FOR XML EXPLICIT, we are thinking about creating an assembly that calls this stored proc, gets back the 7 tables and builds the XML using the DOM in .NET, t
hen in BizTalk referencing that assembly, mapping and sending. Does this seem like a good solution? One of the things we are experiencing building this XML using the DOM in .Net is that it is very very resource expensive. Our stored proc returns upward
s of 20,000 rows of data, and in order to build the XML it requires a lot of looping. In some cases we have nested for loops 2 deep producing a O(n) = n^3. Obviously something will have to be done here because the hit we take from these nested for loops
are HUGE. If anyone has any other advise to add it would be greatly appreciated.
Thanks,
Jim
| |
|
| Anyone?
----- Jim wrote: -----
Thanks for the reply Ehab. What you have said makes a lot of sense. Yes, by variable I meant that we do have certain conditions that produce a different table structure over another if certain conditions are met/omitted. I can see where this is di
fficult to handle because the original schema generated by XMLDATA could be different if certain conditions occur. Because our stored proc is very complicated and contains numerous aggregates which FOR XML does not allow (Although this could be worked ar
ound using temp tables), and because we would have to do some very complicated structure building using FOR XML EXPLICIT, we are thinking about creating an assembly that calls this stored proc, gets back the 7 tables and builds the XML using the DOM in .N
ET, then in BizTalk referencing that assembly, mapping and sending. Does this seem like a good solution? One of the things we are experiencing building this XML using the DOM in .Net is that it is very very resource expensive. Our stored proc returns u
pwards of 20,000 rows of data, and in order to build the XML it requires a lot of looping. In some cases we have nested for loops 2 deep producing a O(n) = n^3. Obviously something will have to be done here because the hit we take from these nested for
loops are HUGE. If anyone has any other advise to add it would be greatly appreciated.
Thanks,
Jim
|
|
|
|
|