Invoke-SqlCmd doesn't return long string?

Question

For the following code

$sql = "select ..... for xml path('row')" # returns a long xml file
$x = Invoke-SqlCmd -Server xxxx $sql
$r = $x[0].ItemArray[0]

The returned $r has a truncated xml string. How to make sure the full long string is returned?

Answer 1

That cmdlet has a default max character length, defaults to 4,000 characters (see Search for -MaxCharLength) for XML or char data types.

Try the following:

$x = Invoke-SqlCmd -Server xxxx $sql -MaxCharLength <some large enough number>

Answer 2

Many times this solves the case:

$x = Invoke-SqlCmd -Server xxxx $sql -MaxCharLength <some large enough number>

However, there are some cases where it doesn't work:

• Somewhere between 80,000 and 3,500,000 chars this solution appears to break down.
• The result I got was scrambled: Inner XML broke outer XML, so clearly at least our version has some defects in it as well.

You could try couple of solutions:

• Limit content to x chars, such as 80,000 and don't try to export anything longer than that. I didn't test if this would solve the defect case also, so if someone else has this problem, please comment if this helps or not.
• I exported everything as CSV, broke the inner XML, created temporary XML result and finally fixed again the inner XML back. This solution worked. Option "-raw" with file reading was necessary when handling files almost one GB size to improve performance.