Target string size is too small to represent the XML instance

[Naveen_Kumar]

I have a field which stores query plans(XML data type) ,when i try to show those plans in report using table visualization getting the below mentioned error.

Error: "Target string size is too small to represent the XML instance. The exception was raised by the IDataReader interface."

Note: Using SQL Server Direct Query method to load the data into reports .

any help will be appriciated .

[v-jiascu-msft]

Hi @Naveen_Kumar,

It seems this issue is from the Server side. I tested with two versions of the Desktop. No issues. Please refer to problem-with-managed-code-simple-example-target-string-size-is-too-small-to-represent-the-xml. Please upgrade your Desktop to the latest version.

My test environment:

1. SQL Server 2016;

2. Direct Query mode in the Desktop 2.55.5010.621 64-bit (February 2018);

3. A Sql table with one column of type xml;

4. Created a table visual.

Best Regards,

Dale

[Naveen_Kumar]

Hi @v-jiascu-msft

I'm using latest Desktop version (feb 2018) only but with Azure sql server . the avg size of xml field is 162340 Bytes

My test environment:

1. Azure SQL Server

2. Direct Query mode in the Desktop 2.55.5010.621 64-bit (February 2018);

3. A Sql table with one column of type xml;

4. Created a table visual.

[Naveen_Kumar]

Hi @v-jiascu-msft

When i used SQL server Import method to get the data into report it is working as expected. As the size of the report is limited (in Import) we cannot use this approach . Please suggest

Thanks & Regards,

Naveen.

[v-jiascu-msft]

Hi @Naveen_Kumar,

I have reported this issue to the Product Team. I will update the news here.

Best Regards,

Dale

[v-jiascu-msft]

Hi @Naveen_Kumar,

I have gotten replies from the Product Team. 

This is one of many by design limitations of DirectQuery models. Fundamentally speaking, an Import model processes all kinds of external data types into internal data types which Power BI engine supports so that all following operations can work on the internally supported data types. But DirectQuery models must push operations to the remote database which doesn't support all BI operations. There are many BI operations which are not supported for XML data type. Currently, Power BI casts XML data type to NVARCHAR(4000) data type in DirectQuery mode. When the data is longer than 4000 characters, users will get the error in this incident.  In general, XML is not a supported data type for DirectQuery models.

Best Regards,

Dale

[Yashaswini13]

Hi,

I am facing the same issue. Do we have any alternative for this or how to proceed further?

Thanks,

Yashaswini