0

I have stored procedures in SQL Server that output data as xml datatype. When I try to get the output parameter in PHP with PHP Driver for SQL Server (sqlsrv extension) and the string is longer than 4000 I get an SQL Error (SQLSTATE 01004 (Data truncated)).

Here is a short example:

Stored Procedure:

CREATE PROCEDURE pr_getlargexml
    @xml xml OUTPUT
AS
    BEGIN
        SET NOCOUNT ON;

        SET @xml =
            (
                SELECT *
                FROM   largeTable
                FOR XML RAW, ELEMENTS, TYPE, ROOT('xml')
            );
    END;
GO

PHP code:

$serverName = "localhost\SQL2012TEST";
$connectionInfo = array("Database" => "TEST", "UID" => "", "PWD" => "");
$conn = sqlsrv_connect($serverName, $connectionInfo);

if ($conn === false) {
    die(print_r(sqlsrv_errors(), true));
}
$retval = '';
$query = sqlsrv_query($conn, 'EXEC pr_getlargexml  ?',array(array(&$retval,SQLSRV_PARAM_OUT,SQLSRV_PHPTYPE_STRING('UTF-8'),SQLSRV_SQLTYPE_XML)));
if ($query === false) {
    die(print_r(sqlsrv_errors(), true));
}

echo "<pre>";
\var_dump($retval);
echo "</pre>";

If the xml is shorter than 4000, it works fine. I also tried to use nvarchar(max) as datatype in sql and SQLSRV_SQLTYPE_NVARCHAR('max') in php as sqltype, but I get the same Error.

Has anyone a solution for my problem?

MrMirCannae
  • 9
  • 3
  • There is also a limit to show message/output of query in sql output window.I am thinking you are confused during the display of output in sql query window as it's maximum length has been reached and you are getting the expected output .Please check the similar answer https://stackoverflow.com/questions/2742651/xml-output-is-truncated-in-sql – AGaur Apr 22 '20 at 09:51
  • It's probably a driver [issue](https://github.com/microsoft/msphpsql/blob/master/CHANGELOG.md#fixed-in-sqlsrv) (_Fixed string truncation when binding varchar(max), nvarchar(max), varbinary(max), and xml types (issue #231)_), although I'm able to reproduce the same error with driver with PHP 7.1.12 and PHP Driver for SQL Server 4.3.0+9904. Perhaps you should look for a workaround. – Zhorov Apr 22 '20 at 11:04

0 Answers0