I'm building a Web Service with the CLR 2.0 that returns a DataTable but I get the followng error: System.InvalidOperationException: There was an error generating the XML.

I'm trying to save and load a DataTable in PowerShell.I save it like this:

and load it like this:

But the type I get back is an array of Rows rather than a DataTable!This is causing me major problems as it needs to be passed into a function which requires a DataTable, and it cannot be cast to one.

Any help greatly appreciated, thanks.

1 Answer

This is a known trap: PowerShell processes your DataTable as a collection of DataRow items. That is the very first command

already “forgets” the data table. You may take a look at the output file, it starts with DataRow:

To avoid this effect, use the , operator (it looks funny but that’s exactly how it works):

As a result, the output file now starts with DataTable:

After that you can import the table back:

Let’s check it:

We can see the same effect (DataRow instead of DataTable). Thus, the correct command is with ,:

So, we really dehydrate a DataTable in this way.

Error Serializing Value 'table 1' Of Type 'system.data.datatable

EDIT: This effect is known as unrolling. PowerShell tends to unroll collections. The comma operator creates an array of a single item. PowerShell unrolls this array, too, but it does not unroll its items (our DataTable).

Here is a very similar question:

Unable To Create A Constant Value Of Type

Error Serializing Value 'table' Of Type 'system.data.datatable.'

Not the answer you're looking for? Browse other questions tagged powershellxml-serializationdatatablexml-deserialization or ask your own question.