0

How can I load a XML file as a SINGLE_BLOB (i.e. single row and single column) to SQL Server with PowerShell?

The code shown here works with T-SQL (column MyXml is of XML datatype):

INSERT INTO dbo.MyTable (MyXML)
    SELECT CAST(bulkColumn AS xml) AS hello
    FROM OPENROWSET(BULK 'C:\Text1.txt', SINGLE_BLOB) AS DATA;

The code shown below however does not work. Error messages seem to be related to the string and I guess problems with the single quotes:

Incorrect syntax near...

$hello = [string](Get-Content -Path "C:\Text1.txt")

$QUERY = "INSERT INTO dbo.MyTable  (MyXML) VALUES ('$hello')";
Invoke-Sqlcmd -ServerInstance . -Database MyDB -Query $QUERY;

If I cast to XML, then I only insert the text System.Xml.XmlDocument and not the whole XML document

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
xhr489
  • 1,957
  • 13
  • 39
  • 2
    `Invoke-Sqlcmd` is an easy but also lousy way to do parameterized queries, since it forces you to use string interpolation, with all sorts of escaping and injection issues. Use the ADO.NET classes (`SqlConnection`, `SqlCommand`), they're available from within PowerShell and do allow parameterization. [Here's one we prepared earlier](https://stackoverflow.com/a/50582924/4137916). I'm not sure if that example will work flawlessly for "very large" data (exceeding 8000 characters); if not it's fixable. – Jeroen Mostert May 30 '22 at 16:13
  • @JeroenMostert: I think the problem is that putting single quotes around the content of the file does not produce a valid string for T-SQL. – xhr489 May 30 '22 at 17:56
  • @JeroenMostert, so you are saying that if I use the standard connection method then the string will work? – xhr489 May 30 '22 at 17:58
  • 1
    If you pass the value as a parameter through `SqlCommand.Parameters`, escaping is not an issue, so yes, that should work. The length may still be an issue if you're planning on sending very large values this way; that may require some special code to stream the file instead. – Jeroen Mostert May 30 '22 at 18:00
  • @JeroenMostert: regarding the length of the string: Can you please say what I should search for if that ends up being a problem? – xhr489 May 30 '22 at 18:02
  • 1
    If the string exceeds 4000 (Unicode) or 8000 (non-Unicode) characters, I don't know if the default `.AddWithValue` call will handle this correctly (but I can't test at the moment). Should this be an issue you can use the overload of `SqlParameters.Add` that allows you to specify an explicit type and size (set the size to -1 to use the `MAX`) types. To stream the entire file without first reading it into a string, use `[IO.File]::OpenText(..)` instead to get a `TextReader` and pass that as the value, with the parameter type set to `Xml`. – Jeroen Mostert May 30 '22 at 18:07
  • @JeroenMostert: Hi well it works with the link from your first comment, I just don't understand what is going on. Also regarding your previous comment: do you mean to adjust the functions you linked to in your first comment? – xhr489 May 30 '22 at 19:17

0 Answers0