Invoking SQL Server STORED PROCEDURE ended with'FOR XML' statement from TIBCO BussinessWorks getting an incorrect result.
Article ID: KB0085710
Updated On:
| Products | Versions |
|---|---|
| TIBCO ActiveMatrix BusinessWorks | - |
| Not Applicable | - |
Description
Resolution:
Description:
==========
Invoking SQL Server STORED PROCEDURE ended with 'FOR XML' statement from TIBCO BussinessWorks (BW) getting an incorrect result.
Environment:
===========
All versions of TIBCO BusinessWorks with JDBC Call Procedure Activity plugin
SQL Server 2000, 2005 and 2008
Symptoms:
=========
1). In SQL Server, define a STORED PROCEDURE(SP) . This SP uses the XML Support feature of SQL Server. It looks like the following:
---------------------------------------------
SELECT * FROM table FOR XML AUTO
---------------------------------------------
Insert some test data into this table with long characters on any columns. (More than 2100 characters).
2). In BW, define a process to call the SP using the JDBC Call Procedure Activity.
3) .Test the process. Check the output of the JDBC Call Procedure Activity. One record in the database table is broken up into several chunks with a special alias name for each Chunk: 'XML_F52E2B61-18A1-11d1-B105-00805F49916B'
Cause:
======
For maximum XML publishing performance. FOR XML does steaming XML formatting of the resulting rowset and directly sends its output to the server side TDS code in small chunks without buffering whole XML in the server space. The chunk size is 2033 UCS-2 characters. XML larger than 2033 UCS-2 characters is sent to the client side in multiple rows each containing a chunk of the XML. SQL Server uses a predefined column name for this rowset with one column of type NTEXT - “XML_F52E2B61-18A1-11d1-B105-00805F49916B” – to indicate chunked XML rowset in UTF-16 encoding. This requires special handling of the XML chunk rowset by the APIs to expose it as a single XML instance on the client side.
On BW side, the JDBC Call Procedure Activity does no special handling on the XML chunk, thus the entire XML string is spitted.
Resolution:
==========
Instead of using 'Select .... FOR XML' statement at the end of a SP in SQL SERVER, define a temporary variable and assign the value of XML to this variable and return this variable.
Example:
= = = = =
---------------------------------------------
DECLARE @x XML
SET @x = (SELECT * FROM table FOR XML AUTO)
SELECT @x
---------------------------------------------
Description:
==========
Invoking SQL Server STORED PROCEDURE ended with 'FOR XML' statement from TIBCO BussinessWorks (BW) getting an incorrect result.
Environment:
===========
All versions of TIBCO BusinessWorks with JDBC Call Procedure Activity plugin
SQL Server 2000, 2005 and 2008
Symptoms:
=========
1). In SQL Server, define a STORED PROCEDURE(SP) . This SP uses the XML Support feature of SQL Server. It looks like the following:
---------------------------------------------
SELECT * FROM table FOR XML AUTO
---------------------------------------------
Insert some test data into this table with long characters on any columns. (More than 2100 characters).
2). In BW, define a process to call the SP using the JDBC Call Procedure Activity.
3) .Test the process. Check the output of the JDBC Call Procedure Activity. One record in the database table is broken up into several chunks with a special alias name for each Chunk: 'XML_F52E2B61-18A1-11d1-B105-00805F49916B'
Cause:
======
For maximum XML publishing performance. FOR XML does steaming XML formatting of the resulting rowset and directly sends its output to the server side TDS code in small chunks without buffering whole XML in the server space. The chunk size is 2033 UCS-2 characters. XML larger than 2033 UCS-2 characters is sent to the client side in multiple rows each containing a chunk of the XML. SQL Server uses a predefined column name for this rowset with one column of type NTEXT - “XML_F52E2B61-18A1-11d1-B105-00805F49916B” – to indicate chunked XML rowset in UTF-16 encoding. This requires special handling of the XML chunk rowset by the APIs to expose it as a single XML instance on the client side.
On BW side, the JDBC Call Procedure Activity does no special handling on the XML chunk, thus the entire XML string is spitted.
Resolution:
==========
Instead of using 'Select .... FOR XML' statement at the end of a SP in SQL SERVER, define a temporary variable and assign the value of XML to this variable and return this variable.
Example:
= = = = =
---------------------------------------------
DECLARE @x XML
SET @x = (SELECT * FROM table FOR XML AUTO)
SELECT @x
---------------------------------------------
Issue/Introduction
Invoking SQL Server STORED PROCEDURE ended with'FOR XML' statement from TIBCO BussinessWorks getting an incorrect result.
Feedback
Yes
No
Powered by
