Hi all,
Is it possible to use StoredProcedurewithSignature to get output from multiple tables and map it in a structured webMethods document type in a single stored procedure.
e.g. A Header record which has say few columns and under that header there are several detail records with multiple columns.
Can we pass a single key value to a stored procedure which will query all the orders associated with that key and all the detail lines associated with each of the orders AND map the output in a webMethods document type. In pipeline when u view the webMethods document types you should say each order and its corresponding lines in hierarchical structure.
Can some one give the hints or sample stored proc written for SQL server database. (Stored procs should be similar for SQL server and Oracle but still, if you have a choice pls send samples for SQL server database)
while we were discussing one of the following suggestions came up.
Formatting the stored procedure output in an xml sting format. even though there are multiple rows and columns in the output; format the output in such a way that it will be single output fields which will contain an xml string.
webMethods can convert this string in document type.
can some on throw some like on performance impact when the stored procedure is formated into an xml string and when the stored procedure is rub to query the line detail data by querying for each line
For the time being we are using regular StoredProcedure Adapter template instead of StoredProcedurewithSignature. What we are doing is we have defined two ResultSets in the output of StoredProcedure Adapter service. The first resultset always return one record and second resultset results multiple records related to first reocrd. This works with two relational tables.
If you get resultset from a third table then you need to write your own code to relate the records form second and third table.