将文件内容插入 SQL Server 表

发布于 2024-11-30 17:24:43 字数 1014 浏览 0 评论 0原文

我正在使用简单的 PowerShell 脚本从 Web 服务检索一些 XML 数据。 XML 正在写入文件，但我想修改脚本并将 XML 插入 SQL Server 表中。我怎样才能做到这一点？

cls

$apiKey = "00000000-1111-2222-3333-444444444444"
$userName   = "12345678"
$password   = "SamplePassword"

$URI     = "https://www.example.com/api/TestWS.TestService.svc?wsdl" 
$prox = New-WebServiceProxy -uri $URI -namespace WebServiceProxy

$chambersListData = $prox.chambersList($userName, $password, $apiKey, 0, $false, 0, $false)
$chambersListData | Get-Member

for($pageNumber = 1; $pageNumber -lt $chambersListData.pagesCount ; $pageNumber++)
{
    $Page = $prox.chambersList($userName, $password, $apiKey, $pageNumber, $true, 0, $false)
    $Page.chambersList | Export-Clixml ("C:\TEST_EXPORT\chambersList"+$pageNumber+".txt") -Encoding UTF8
}

示例 INSERT 可能类似于：

INSERT INTO [mydatabase].[test].[TestExportXml]
(
[MethodName],
[XmlData]
)
SELECT
'chambersList',
* here comes the C:\TEST_EXPORT\chambersList"+$pageNumber+".txt file content *

原文

I'm using simple PowerShell script to retrieve some XML data from a Web Service.
The XML is being written to a file, but I would like to modify the script and insert the XML into SQL Server table instead. How can I do that?

cls

$apiKey = "00000000-1111-2222-3333-444444444444"
$userName   = "12345678"
$password   = "SamplePassword"

$URI     = "https://www.example.com/api/TestWS.TestService.svc?wsdl" 
$prox = New-WebServiceProxy -uri $URI -namespace WebServiceProxy

$chambersListData = $prox.chambersList($userName, $password, $apiKey, 0, $false, 0, $false)
$chambersListData | Get-Member

for($pageNumber = 1; $pageNumber -lt $chambersListData.pagesCount ; $pageNumber++)
{
    $Page = $prox.chambersList($userName, $password, $apiKey, $pageNumber, $true, 0, $false)
    $Page.chambersList | Export-Clixml ("C:\TEST_EXPORT\chambersList"+$pageNumber+".txt") -Encoding UTF8
}

Sample INSERT could be similar to:

INSERT INTO [mydatabase].[test].[TestExportXml]
(
[MethodName],
[XmlData]
)
SELECT
'chambersList',
* here comes the C:\TEST_EXPORT\chambersList"+$pageNumber+".txt file content *

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初雪 2024-12-07 17:24:43

是否要先创建文件，然后将文件导入到 SQL Server 表中？如果是这样，您可以按如下方式使用 T-SQL OPENROWSET：

INSERT INTO dbo.TestExportXml (MethodName, XmlData)
   SELECT 'chambersList' AS MethodName,*
    FROM OPENROWSET(BULK N'E:\test.xml', SINGLE_BLOB) AS XmlData

您可以使用 Invoke-Sqlcmd cmdlet 调用 T-SQL 语句或使用此函数：

http://poshcode.org/2279

Do you want to first create the file and then import the file into a SQL Server table? If so you can use the T-SQL OPENROWSET as follows:

INSERT INTO dbo.TestExportXml (MethodName, XmlData)
   SELECT 'chambersList' AS MethodName,*
    FROM OPENROWSET(BULK N'E:\test.xml', SINGLE_BLOB) AS XmlData

You could call the T-SQL statement with the Invoke-Sqlcmd cmdlet or use this function:

http://poshcode.org/2279

回复收藏 0 原文

~没有更多了~