PowerShell XML to SQL Server

Question

I have composed a very simple code to pass my XML export to a SQL Server database. Everything works and the data appears in the table as it should.

However when I tested with one single record from the list (around 120k of them) and it took me around 20sec. It must be a better way to pass information across. I attached the code below for people who know how to do it. I guess my code is very simple and works with the principles and not necessary the right way.

Since I produce an XML every day it is really important that I speed this procedure up. As I have never done it before and eager to learn any comments/links are very welcome. Thank you for anyone's time in advance.

$sqlserver="test"
$db="test"
$table="dbo.test"
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$sqlserver; Database=$db; Integrated Security = True;"
$conn.Open()


$cmd=$conn.CreateCommand()
[xml]$xmllog="<logroot>$(get-content("I:\somefile.xml"))</logroot>"
$idevent=$xmllog.logroot.Event.system.eventid[0]
$levelcode=$xmllog.logroot.Event.system.level[0]
$times=$xmllog.logroot.Event.system.timecreated.systemtime[0]
$computers=$xmllog.logroot.Event.system.computer[0]
$subUser=$xmllog.logroot.Event.selectsinglenode("//*[@Name='SubjectUserName']")[0].'#text'
$subdomain=$xmllog.logroot.Event.selectsinglenode("//*[@Name='SubjectDomainName']")[0].'#text'
$targUser=$xmllog.logroot.Event.selectsinglenode("//*[@Name='TargetUserName']")[0].'#text'
$targetDom=$xmllog.logroot.Event.selectsinglenode("//*[@Name='TargetDomainName']")[0].'#text'
$logontypes=$xmllog.logroot.Event.selectsinglenode("//*[@Name='LogonType']")[0].'#text'
$logonproc=$xmllog.logroot.Event.selectsinglenode("//*[@Name='LogonProcessName']")[0].'#text'
$workstation=$xmllog.logroot.Event.selectsinglenode("//*[@Name='WorkstationName']")[0].'#text'
$ipaddress=$xmllog.logroot.Event.selectsinglenode("//*[@Name='IpAddress']")[0].'#text'

$cmd.CommandText="insert dbo.test  values (1, '$idevent', '$levelcode', '$times','$computers', '$subUser', '$subdomain','$targUser', '$targetDom', '$logontypes', '$logonproc', '$workstation','$ipaddress')"

$cmd.ExecuteNonQuery()
$conn.Close()

Answer 1

You are accessing the properties and executing SelectSingleNode() on every 120k nodes each time you set a variable, and then you just keep the first result for each. What you should do is pick the first event-node and run SelectSingleNode() etc. on that.

Ex.

$event = $xmllog.logroot.Event[0]

    $idevent=$event.system.eventid
    $levelcode=$event.system.level
    $times=$event.system.timecreated.systemtime
    $computers=$event.system.computer
    $subUser=$event.selectsinglenode("//*[@Name='SubjectUserName']").'#text'
    #etc...

I'm not sure what your xml-looks like, but to test I used this:

$xmllog = [xml]@"
<logroot>
    <Event>
        <System>
            <Eventid>1</Eventid>
            <Level>3</Level>
            <Computer>Computer1</Computer>
            <Timecreated systemtime="10:24" />
            <Attribute Name="SubjectUserName">User1</Attribute>
            <Attribute Name="SubjectDomainName">DomainA</Attribute>
            <Attribute Name="TargetUserName">User11</Attribute>
            <Attribute Name="TargetDomainName">DomainB</Attribute>
            <Attribute Name="LogonType">Windows</Attribute>
            <Attribute Name="LogonProcessName">Winlogon.exe</Attribute>
            <Attribute Name="WorkstationName">Computer1</Attribute>
            <Attribute Name="IpAddress">192.168.1.10</Attribute>
        </System>
    </Event>
        <Event>
        <System>
            <Eventid>1</Eventid>
            <Level>3</Level>
            <Computer>Computer2</Computer>
            <Timecreated systemtime="10:24" />
            <Attribute Name="SubjectUserName">User2</Attribute>
            <Attribute Name="SubjectDomainName">DomainA</Attribute>
            <Attribute Name="TargetUserName">User21</Attribute>
            <Attribute Name="TargetDomainName">DomainB</Attribute>
            <Attribute Name="LogonType">Windows</Attribute>
            <Attribute Name="LogonProcessName">Winlogon.exe</Attribute>
            <Attribute Name="WorkstationName">Computer2</Attribute>
            <Attribute Name="IpAddress">192.168.1.11</Attribute>
        </System>
    </Event>
</logroot>
"@

To further speed it up when processing all events, you could try something like this to batch import using a single INSERT-statement. I'm not sure what your batch size limit is. If the query gets to big, you can split it up for each ex. each 10k row.

$sqlserver="test"
$db="test"
$table="dbo.test"
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$sqlserver; Database=$db; Integrated Security = True;"
$conn.Open()

$cmd=$conn.CreateCommand()
[xml]$xmllog="<logroot>$(get-content("I:\somefile.xml"))</logroot>"

$rows = @()

foreach ($event in $xmllog.logroot.Event) {

    $idevent=$event.system.eventid
    $levelcode=$event.system.level
    $times=$event.system.timecreated.systemtime
    $computers=$event.system.computer
    $subUser=$event.selectsinglenode("//*[@Name='SubjectUserName']").'#text'
    $subdomain=$event.selectsinglenode("//*[@Name='SubjectDomainName']").'#text'
    $targUser=$event.selectsinglenode("//*[@Name='TargetUserName']").'#text'
    $targetDom=$event.selectsinglenode("//*[@Name='TargetDomainName']").'#text'
    $logontypes=$event.selectsinglenode("//*[@Name='LogonType']").'#text'
    $logonproc=$event.selectsinglenode("//*[@Name='LogonProcessName']").'#text'
    $workstation=$event.selectsinglenode("//*[@Name='WorkstationName']").'#text'
    $ipaddress=$event.selectsinglenode("//*[@Name='IpAddress']").'#text'

    $rows += "(1, '$idevent', '$levelcode', '$times','$computers', '$subUser', '$subdomain','$targUser', '$targetDom', '$logontypes', '$logonproc', '$workstation','$ipaddress')"
}

#Combine value-strings
$values = $rows -join ", " 
$cmd.CommandText = "insert into $table values $values;"

$cmd.ExecuteNonQuery()
$conn.Close()

CommandText would be like this with the sample xml above:

"insert into dbo.test values (1, '1', '3', '10:24','Computer1', 'User1', 'DomainA','User11', 'DomainB', 'Windows', 'Winlogon.exe', 'Computer1','192.168.1.10'), (1, '1', '3', '10:24','Computer2', 'User1', 'DomainA','User11', 'DomainB', 'Windows', 'Winlogon.exe', 'Computer1','192.168.1.10');"

Warning: Consider using parameterized queries unless you really trust the input data. This query is prone to sql injection.