使用一列将XML文件加载到SQL Server，从PowerShell加载一行

发布于 2025-02-02 21:48:00 字数 682 浏览 4 评论 0原文

如何使用PowerShell加载XML文件作为单个_BLOB（即单行和单列）到SQL Server？

此处显示的代码与T-SQL一起使用（列myxml是XML datatype）：

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

下面显示的代码，但是不起作用。错误消息似乎与字符串有关，我想单个引号的问题：

不正确的语法附近...

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

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

如果我投射到xml，那么我只插入文本system.xml.xmldocument而不是整个XML文档

原文

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

分享到QQ

分享到微博