Hello,

I am trying to generate a schema for a stored procedure which will be called
periodically to look for new data. The stored procedure will return the new
records in XML format from different but related tables.

------------------------------------------------------

CREATE PROCEDURE [dbo].[MonitorForNewRecords] AS

DECLARE @DocNumber AS varchar(11);

SELECT TOP 1 @DocNumber = DOC_ID
FROM COMMON_TABLE
WHERE STATUS_CODE = '1';

IF Len(@DocNumber) > 0
BEGIN

UPDATE COMMON_TABLE
SET STATUS_CODE = 2
WHERE DOC_ID = @DocNumber;


SELECT [CT1],[CT2], [CT3],
(
SELECT [A1],[A2],[A3]
FROM [A]
WHERE @DocNumber = [A].[DOC_ID]
FOR XML AUTO, TYPE
),
(
SELECT [B1],[B2],[B3]
FROM [B]
WHERE @DocNumber = [B].[DOC_ID]
FOR XML AUTO, TYPE
)
FROM [COMMON_TABLE]
WHERE @Doc_Number = [COMMON_TABLE].[DOC_ID]
FOR XML AUTO, TYPE
END

--------------------------------------------------------


While there is only one entry into the COMMON_TABLE for each DOC_ID, tables
B and C may contain multiple records with the same DOC_ID.


I have tried adding XMLDATA to the main SELECT statement, but BizTalk just
generates a schema for the COMMON_TABLE fields only. I have tried adding
XMLDATA to the inner SELECT statements as well, but then BizTalk doesn't
generate any schema at all (no error is given, at the end of the wizard I
just get nothing).
Can I retrieve the contents of tables COMMON_TABLE, A and B using a single
stored procedure? If so, how can I make BizTalk generate a schema for it?

Thanks in advance for any response.

[ Post a follow-up to this message ]

Steve,

Of course you have to use XMLDATA. The Schema Generating Wizard would use
the schema generated by this clause.
I think you have problems with the code for Resultset.
Try to generate schema for
SELECT [CT1],[CT2], [CT3],
FROM [COMMON_TABLE]
WHERE @Doc_Number = [COMMON_TABLE].[DOC_ID]
FOR XML AUTO, XMLDATA

save it and visualy compare with your variant.
Maybe the  "FOR XML AUTO, XMLDATA" clause for your variant has some
diffuculties?

Anyway, you can use a table-variable or temp-table for creating interim
table and generate the resultset for it.

--
Regards,

Leonid Ganeline
BizTalk Solution Developer
===================================
BizTalk Blog -- http://geekswithblogs.net/leonidganeline

"Steve" <Steve@discussions.microsoft.com> wrote in message
news:F8D35D4A-7456-4EEB-B4CE-EDF0F46C1C1E@microsoft.com...
> Hello,
>
> I am trying to generate a schema for a stored procedure which will be
> called
> periodically to look for new data. The stored procedure will return the
> new
> records in XML format from different but related tables.
>
> ------------------------------------------------------
>
> CREATE PROCEDURE [dbo].[MonitorForNewRecords] AS
>
> DECLARE @DocNumber AS varchar(11);
>
> SELECT TOP 1 @DocNumber = DOC_ID
> FROM COMMON_TABLE
> WHERE STATUS_CODE = '1';
>
> IF Len(@DocNumber) > 0
> BEGIN
>
> UPDATE COMMON_TABLE
> SET STATUS_CODE = 2
> WHERE DOC_ID = @DocNumber;
>
>
> SELECT [CT1],[CT2], [CT3],
> (
>   SELECT [A1],[A2],[A3]
>   FROM [A]
>   WHERE @DocNumber = [A].[DOC_ID]
>   FOR XML AUTO, TYPE
> ),
> (
>   SELECT [B1],[B2],[B3]
>   FROM [B]
>   WHERE @DocNumber = [B].[DOC_ID]
>   FOR XML AUTO, TYPE
> )
>   FROM [COMMON_TABLE]
>   WHERE @Doc_Number = [COMMON_TABLE].[DOC_ID]
>   FOR XML AUTO, TYPE
> END
>
> --------------------------------------------------------
>
>
> While there is only one entry into the COMMON_TABLE for each DOC_ID,
> tables
> B and C may contain multiple records with the same DOC_ID.
>
>
> I have tried adding XMLDATA to the main SELECT statement, but BizTalk just
> generates a schema for the COMMON_TABLE fields only. I have tried adding
> XMLDATA to the inner SELECT statements as well, but then BizTalk doesn't
> generate any schema at all (no error is given, at the end of the wizard I
> just get nothing).
> Can I retrieve the contents of tables COMMON_TABLE, A and B using a single
> stored procedure? If so, how can I make BizTalk generate a schema for it?
>
> Thanks in advance for any response.

[ Post a follow-up to this message ]