|
Home > Archive > BizTalk Server General > May 2006 > Problem with SQL Adapter - generating schema
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 |
Problem with SQL Adapter - generating schema
|
|
|
| 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.
| |
| Leonid Ganeline 2006-05-29, 5:21 pm |
| 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.
|
|
|
|
|