CODEWITHSUNDEEP
sql-serverpowershell
ca9163d9
ca9163d9

Reputation: 29159

Invoke-SqlCmd doesn't return long string?

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?

Upvotes: 21

Views: 14319

Answers (2)

New Guy
New Guy

Reputation: 9126

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>

Upvotes: 33

Timo Riikonen
Timo Riikonen

Reputation: 61

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.

Upvotes: 4

Related Questions