importXmlToSql.psm1
|
Import-Module SqlServer $Global:valueStrings = "values(" $Global:columnsString ="insert into $TableName(" <# .Synopsis DImport an xml to sql table .Description Import xml to sql table .Parameter SqlServer Sql server instance .Parameter DatabaseName Database name .Parameter Username Username for sql server instance. If this value is null, we consider as being Integrated Security = true .Parameter Password Password for sql server instance. .Parameter ConnectionString ConnectionString to sql server. .Parameter XmlFile Path to xml that has to be imported .Parameter Mapping A hashtable that contains the mapping of node/attribute to column table. The form of this table should be: 'pathToNode'='columnName' .Example Map a xml node inner text to a column named column1 Xml: <a> <b> <c>Test</c> </b> </a> Import-XmlToSql -ConnectionString "connectionString" -XmlFile "xmlFilePath" -Mapping @{"a.b.c"="Column1"} .Example Map a xml node attribute attr to a column named column2 Xml: <a> <b attr="aa"> <c>Test</c> </b> </a> Import-XmlToSql -ConnectionString "connectionString" -XmlFile "xmlFilePath" -Mapping @{"a.b[attr]"="Column2"} #> Function Import-XmlToSql { param ( [Parameter(Mandatory=$true,ParameterSetName="sql")] [ValidateNotNullOrEmpty()] [string] $SqlServer, [Parameter(Mandatory=$true,ParameterSetName="sql")] [ValidateNotNullOrEmpty()] [string] $DatabaseName, [Parameter(Mandatory=$false,ParameterSetName="sql")] [ValidateNotNullOrEmpty()] [string] $Username, [Parameter(Mandatory=$false,ParameterSetName="sql")] [ValidateNotNullOrEmpty()] [securestring] $Password, [Parameter(Mandatory=$true,ParameterSetName="connectionString")] [ValidateNotNullOrEmpty()] [string] $ConnectionString, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string] $TableName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string] $XmlFile, [Parameter(Mandatory=$true)] [hashtable] $Mapping ) $xmldocument = New-Object System.xml.XmlDocument $xmlDocument.Load($XmlFile) if([string]::IsNullOrWhiteSpace($ConnectionString) -eq $true) { $ConnectionString = Get-ConnectionString -SqlServer $SqlServer -DatabaseName $DatabaseName -Username $Username -Password $Password } $result = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query "SELECT COUNT(*) AS Count FROM $TableName" -As DataRows if($result -ne $null) { foreach($xmlNode in $xmldocument.root.ChildNodes) { $Global:valueStrings = "values(" $Global:columnsString ="insert into $TableName(" Execute-Command -XmlNode $xmlNode -Mapping $Mapping $Global:valueStrings=$Global:valueStrings+")" $Global:columnsString=$Global:columnsString+")" $sqlStatement = $columnsString+$valueStrings if($sqlStatement -ne "insert into $TableName()values()") { Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sqlStatement -OutputAs DataRows } } } } Function Get-ConnectionString { param ( [string] $SqlServer, [string] $DatabaseName, [string] $Username, [securestring] $Password ) $connectionBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $connectionBuilder["Data Source"] = $SqlServer $connectionBuilder["Initial Catalog"] = $DatabaseName if([string]::IsNullOrWhiteSpace($Username) -ne $true) { $connectionBuilder["User ID"] = $Username $psCredentials = New-Object System.Management.Automation.PSCredential -ArgumentList $Username, $password $connectionBuilder["Password"] = $psCredentials.GetNetworkCredential().Password } else { $connectionBuilder["integrated Security"] = $true; } return $($connectionBuilder.ConnectionString) } Function Get-NodePath { param ( [System.Xml.XmlNode] $XmlNode ) $localXmlNode = $XmlNode.CloneNode($true) $stringBuilder = New-Object System.Text.StringBuilder if($localXmlNode.Attributes -ne $null) { $localXmlNode.Attributes.RemoveAll() } $result = [string]::Format("{0}",$localXmlNode.Name) $XmlNode = $XmlNode.ParentNode while($XmlNode -ne $null) { $localXmlNode = $XmlNode.CloneNode($true) if($localXmlNode.Attributes -ne $null) { $localXmlNode.Attributes.RemoveAll() } if($localXmlNode.Name.Contains("#") -eq $false) { $result = [string]::Format("{0}.{1}",$localXmlNode.Name,$result) } $XmlNode = $XmlNode.ParentNode } $localXmlNode = $localXmlNode return $result } Function Get-NodeValue { param ( [string] $Key, [System.Xml.XmlNode] $XmlNode ) if($Key.Contains("[")) { $attribute = $Key.Split("[")[1] $attribute = $attribute.TrimEnd("]").TrimStart("[") return $($XmlNode.Attributes[$attribute].Value) } return $($XmlNode.InnerText) } Function Find-Key { param ( [string] $XmlNodePath, [System.Collections.Generic.List[string]] $Keys ) $result = New-Object System.Collections.Generic.LinkedList[string] foreach($key in $Keys) { if($key.Split("[")[0].Equals($XmlNodePath) -eq $true) { $result.AddFirst($key) | Out-Null } } return $result } Function Execute-Command { param ( [System.Xml.XmlNode] $XmlNode, [hashtable] $Mapping ) Process-XmlNode -XmlNode $XmlNode -Mapping $Mapping foreach($xmlNode in $XmlNode.ChildNodes) { Execute-Command -XmlNode $XmlNode -Mapping $Mapping | Out-Null } } Function Process-XmlNode { param ( [System.Xml.XmlNode] $XmlNode, [hashtable] $Mapping ) $nodePath = Get-NodePath -XmlNode $XmlNode $keyList = Find-Key -XmlNodePath $nodePath -Keys $Mapping.Keys foreach($key in $keyList) { $value = Get-NodeValue -Key $key -XmlNode $XmlNode if($value -ne $null) { if($Global:valueStrings.EndsWith("(") -eq $true) { $Global:valueStrings=$Global:valueStrings+"'"+$value.Replace("'","''")+"'" $Global:columnsString = $Global:columnsString+$Mapping[$key] } else { $Global:valueStrings=$Global:valueStrings+","+"'"+$value.Replace("'","''")+"'" $Global:columnsString = $Global:columnsString+","+$Mapping[$key] } } } } Export-ModuleMember -Function Import-XmlToSql |