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