Error when reading/parsing the output as native XML

[Niritl-Madeira]

Hi Daniel,

I’ve been using your code, and I must say it’s fantastic! It has saved me a ton of work. Thank you for sharing it!

However, I encountered a small issue while extracting a large table using the XML option and calling your procedure via PowerShell. Here's the code I’ve been using:

$connectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

# Create a SQL command
$command = $connection.CreateCommand()
$command.CommandText = "EXEC sp_generate_merge @results_to_text = 0, @include_use_db = 0, @max_rows_per_batch = 100, @delete_if_not_matched = 0, @schema = N'dbo', @table_name = N'XXX';"

# Execute the command and read the XML output
$xmlReader = $command.ExecuteXmlReader()

# Load the XML into an XML document
$xmlDocument = New-Object System.Xml.XmlDocument
$xmlDocument.Load($xmlReader)

# Save the XML to a file
$xmlDocument.Save($outputFilePath)

The issue I ran into is that the only way I was able to get the output saved to a file was by changing this line:

SELECT [processing-instruction(x)]=@output FOR XML PATH(''), TYPE;

to:

SELECT @output AS a FOR XML PATH('');

I’m not entirely sure why this change was necessary, but I thought it might be worth bringing to your attention in case there’s something in the XML handling that could be causing it.

Thanks again for your excellent work on this code—it’s been incredibly helpful!

Best regards,
Nirit

[GennadyGS]

@Niritl-Madeira
I encountered the same issue, and your solution helped me too.
Thanks so much!

[dnlnln]

Hi @GennadyGS & @Niritl-Madeira,

The reason for the processing-instruction(x) directive is used in combination with FOR XML is to ensure the SQL is encoded as-is, which is important when executing the tool in environments like SSMS or VSCode query editor (for context see 32bb68c).

The original reason for using FOR XML at all was actually to circumvent those clients' tendency to truncate the result. But given that you're using the tool directly within the SQL client, you shouldn't need to use the XML approach at all, even if you have a very large dataset. Have you considered using this approach?

$ErrorActionPreference = "Stop"

$outputFilePath = 'C:/out/merge.sql'
$serverName = "localhost,1433"
$databaseName = "AdventureWorks"
$schemaName = "Sales"
$tableName = "CurrencyRate"
$connectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True;"

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

# Create a SQL command
$command = $connection.CreateCommand()
$command.CommandText = "EXEC sp_generate_merge @results_to_text = 1, @include_use_db = 0, @max_rows_per_batch = 100, @delete_if_not_matched = 0, @schema = N'$schemaName', @table_name = N'$tableName';"

# Execute the command and read the scalar string output
$outputString = $command.ExecuteScalar()

# Save the string to a file
[System.IO.File]::WriteAllText($outputFilePath, $outputString)

# Close the connection
$connection.Close()