sql server - 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>