Inserting file content into SQL Server table
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 WebSe开发者_StackOverflow社区rviceProxy
$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 *
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
精彩评论