xml resultset from SQL store procedure is splitt into multiple nodes in Logic Apps

I have a stored procedure which returns an XML  result set. when I execute the stored procedure, the output is a single XML as below.

CREATE PROCEDURE [dbo].[TESTSP]

AS

BEGIN

SELECT * from testtable

FOR XML AUTO

END

 

The output is a single xml object as below.

<testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" etail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " /><testtable ID="1" Detail="testdata  " />

 

But however when I execute the same in logic app, the xml was split into multiple nodes in json each with 2033 characters, as below.

Output:

"ResultSets": {

"x": [

{                    "XML_F52E2B61-18A1-11d1-B105-00805F49916B": "<testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><test"

},

{                    "XML_F52E2B61-18A1-11d1-B105-00805F49916B": "table ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/>"

}            ]

}

 

When you return XML from SQL Server, it does indeed split it into 2033-character chunks.  There does not seem to be a way to control this behavior when returning raw XML as a result set. The solution here is to convert the output to NVARCHAR(MAX) before you do so.

https://blogs.msdn.microsoft.com/sqlprogrammability/2006/04/13/what-does-server-side-for-xml-return/. “The chunk size is 2033 UCS-2 characters. Thus, XML larger than 2033 UCS-2 characters is sent to the client side in multiple rows each containing a chunk of the XML.“

The modified SP which fixes the issue,

ALTER PROCEDURE [dbo].[TESTSP]

AS

BEGIN

DECLARE @xml XML;

SET @xml = (SELECT * from testtable FOR XML AUTO);

SELECT CONVERT(NVARCHAR(MAX),@xml);

END

 

After which the output in Logic app is in single node as below.

"body": {        "OutputParameters": {},"ReturnCode": 0,

"ResultSets": {            "Table1": [                {

"Column1": "<testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/><testtable ID=\"1\" Detail=\"testdata  \"/>"

}            ]        }

Further Reads :

https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/ https://www.simple-talk.com/sql/database-administration/converting-string-data-to-xml-and-xml-to-string-data/