SQL fetch returns truncated data (7999 bytes) from XML field in table

I'm queying a SQL database to extract data stored in XML format with in a table. The fetch command returns this datum as a text string, with the string exceeding 8000 characters in length. Matlab returns truncated data and appears to have a size limit for this field, with the maximum length being 7999 characters. I'm unable to find this documented or instructions on how to extend the size of the character string returned. Is there information on how to overcome this data limit? Alternatively, I'm told that there is a way to execute an SQL command to extract or interpret information from XML stored in database. Is there an example of how this is done through the Matlab fetch command?

Answer 1

[Robert Heaton]

I was unable to solve this internally using the Matlab interface/commands, and instead resolved this problem by installing Microsoft SQL Management Server. This package has a command line query function sqlcmd which can return a character string to Matlab through an external call.

To get the data, I used the matlab "system" command to send an SQL query to the database, with the table index value as the SQL search criteria. The key element in the command to resolve this problem is found in the sqlcmd option "-y 0" which avoids truncation of long strings returned from the database. The rest of the code simple breaks the table request into batches (loop not shown) to avoid potential issues with return string length; none occured, so likely not necessary. The return from the system command was a single character array containing embedded new-line characters, so the Matlab "split" command conveniently processed it into a cell array format ammenable to further analysis. Code snippets provided below.

sqlBaseQuery = 'SELECT [Id],[DataXml] FROM [dbo].[PlanFields] WHERE [Id] IN (';

sqlBaseCmd = ['sqlcmd -S "' dbServerName '" -y 0 -d "' Catalog '" -Q "'];

thisSqlQuery = [sqlBaseQuery strjoin(cellfun(@(x) num2str(x),num2cell(FieldData.PlanField_Id(BatchStart:BatchEnd)),'UniformOutput',false),', ') ') ORDER BY [Id] ASC"'];

% submit the command to the O/S

[sqlStatus, sqlResult] = system([sqlBaseCmd thisSqlQuery]);

% break the string into components according to newline position

ProcessRecord = split(sqlResult,newline);