Load xml file to SQL Server with one column and one row from PowerShell

162 Views Asked by At

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

0

There are 0 best solutions below