Shredding XML with Powershell and SQL Server

How can I shred XML using Powershell and SQL Server? I have an XML source which requires to be shredded and placed into Entity-attribute-model (EAV) table.

 The query takes an XML document , inserts into a table , builds an XML string and shreds the xml into a table – applying the Entity-Attribute-model .

The query takes an XML document , inserts into a table , builds an XML string and shreds the xml into a table – applying the Entity-Attribute-model .

This code is tested on SQL Server 2008 R2

DECLARE @XML_str VARCHAR(MAX);
DECLARE @Guitarists XML;
--Insert data in order , into a table variable
DECLARE @XML TABLE (XML_source VARCHAR(2000), OrderRow INT IDENTITY(1,1));
--insert the XML into the table, line by line
INSERT INTO @XML(XML_source)
SELECT "
           
	              1
		          Carlos Santana
		     
		    
	              2
		          Prince
		        
		   
	              3
		          Jimi Hendrix
		        
		

           	
";

--build the XML as a string 
SELECT @XML_str=COALESCE(@XML_str,'') + XML_source 
FROM @XML 
WHERE XML_source IS NOT NULL 
ORDER BY OrderRow;
--save the string into a variable 
SELECT @Guitarists = @XML_str;
-- Create a Pivot of the  XML 
SELECT MAX (CASE WHEN attribute='Guitarist_ID' THEN VALUE ELSE '' END ) AS [Guitarist_ID],
MAX (CASE WHEN attribute='Guitarist_Name' THEN VALUE ELSE '' END ) AS [Guitarist_Name]
FROM 
( 
SELECT --XML shred applying the Entity-attribute-value model
[property].value('(./text())[1]', 'Varchar(20)') AS [Value],
[property].value('@Name', 'Varchar(20)') AS [Attribute],
DENSE_RANK() OVER (ORDER BY [object]) AS Guitarist
FROM @Guitarists.nodes('Musicians/Guitarist') AS b ([object])
CROSS APPLY b.object.nodes('./Property') AS c (property)
) Guitarists
GROUP BY Guitarist;

--outputed results
Guitarist_ID	Guitarist_Name
1	Carlos Santana
2	Prince
3	Jimi Hendrix

 

Read More

 

OpenXML - more than 8000 characters - SQL Server DBA

Powershell for SQL SMO

Export-CSV Powershell

 

SQL Server Export to Excel with Powershell

SQL Server  Export  to Excel with Powershell is useful for all sorts of activities – ETL, management data and reporting are some examples.  This post is a follow up to Export Excel data to SQL Server with Powershell .

In this example, a connection is made to a SQL server Instance , a recordset is returned , placed into a DataSet and then written to a new Excel worksheet.

Using Export-CSV Powershell - SQL Server DBA  produces similar results, but doesn’t give you the granular control  you may require when exporting to Excel.

If you have any questions contact me or leave a comment on this post .

 

#some variables 
$serverName = "myserver\sqlserver1"; 
$databaseName = "master"; 
#the save location for the new Excel file
$filepath = "C:\projects\excelexport.xls"; 

    
   
    #create excel object
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.add()
    $worksheetA = $workbook.Worksheets.Add() 
    #create byUser worksheet 
    $sheet1 = $workbook.worksheets.Item(1)
    $sheet1.name = "server_principals"
    
    #create a Dataset to store the DataTable  
    $dataSet = new-object "System.Data.DataSet" "Server_Principals"
    
    #create a Connection to the SQL Server database
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$serverName;database=$databaseName;Integrated Security=sspi"
    $query= "select principal_id,[name] as principal_name from sys.server_principals;"
    #Create a SQL Data Adapter to place the resultset into the DataSet
    $dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $cn) 
        $dataAdapter.Fill($dataSet) | Out-Null
    #close the connection 
    $cn.Close()
      
    $dataTable = new-object "System.Data.DataTable" "Principals"
    $dataTable = $dataSet.Tables[0]
  
  #iterate through every DataTable line item and insert to the Excel worksheet
    $x=1
    $dataTable | FOREACH-OBJECT{
          
        $sheet1.cells.item($x, 1) =  $_.principal_id
    $sheet1.cells.item($x, 2) =  $_.principal_name
    $x++
          
    }
   
    

   $range1 = $sheet1.UsedRange
   $range1.EntireColumn.AutoFit()
    
   #save excel worksbook
   $excel.ActiveWorkbook.SaveAs("$filepath ")
   $excel.quit()

 Read More

SQL Server – Powershell Excel to HTML - SQL Server DBA

Export Excel data to SQL Server with Powershell - SQL Server DBA

Powershell - run script on all sql servers - SQL Server DBA

Export-CSV Powershell - SQL Server DBA

Number of physical processors with Powershell – number of cores

I want to view  the Physical  Porcessor information on the server . How can I view :

a) The name and amount of processors

b)The DeviceID

c)Number of cores

d)Number of Logical Processors

Use Powershell and Windows Management Instrumentation (WMI) . The Get-WMiObject cmdlet  is great for system administration

 

 

Get-WmiObject –class Win32_processor | ft Name,DeviceID,NumberOfCores,NumberOfLogicalProcessors

 

Read More

 SQL Server – More processor speed or cpu cores

CPU Count

List files in Creation Time order with Powershell

Question: How do I list all files in a directory and order the files by Creation Time in ascending order, using Powershell?    

Answer:  Use  Powershell cmdlets – Get-ChildItem and Sort-Order to list the files in a directory .

If the item is a container, it gets the items inside the container, known as child items. Use the Recurse parameter to get items in all child containers.

The Sort-Object cmdlet sorts objects in ascending or descending order based on the values of properties of the object. In the example below, I’ve select a single property i.e CreationTime.  

 

#An example of listing items in a directory
 Get-ChildItem -path "C:\projects\testing\laboratory"

#An example of listing the items, filtering name and length and using sort-object
Get-ChildItem -path "C:\projects\testing\laboratory"| select name,lastwritetime,CreationTime | sort-object -property CreationTime

 

Read More 

SQL Database Size report using Powershell

SQL Server - Search and Delete files from a text based list with Powershell

SQL Server - Powershell Get-ChildItem with examples

SQL Database Size report using Powershell

Question: Could you supply a Powershell script to  report all SQL Server database sizes  across the environment , broken down in Data  and Logs? This report must run on a regular , on-demand basis.

Answer:  Yes , it’s possible by running a t-sql script across a list of SQL Server Instances using Powershell.   

As long as the instances list is up to date , executing this script will iterate through every SQL Server Instance – execute the t-sql code , and create a html file with the naming convention -  for example : sql_server_health_db_sizes_2013-04-02T153128.html

 

set-location E:\health\SQL_Server

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$instancepath=$basepath + "\config\instances.txt"
$outputfile="\logs\sql_server_db_sizes_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile


$filePath = ""

$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
$instance
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "
select @@servername as ServerName,
DB_NAME(mf.database_id) as [db_name],
type_desc,
str(convert(dec(14,2),size) / 128,14,2) as DATABASE_SIZE_MB,
str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) as DATABASE_SIZE_GB
from sys.master_files as mf
INNER JOIN sys.databases as da ON da.database_id = mf.database_id
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "SQL Server Database Sizes" | Set-Content $outputfilefull  

$filepath = $outputfilefull  

 Read More

SQL Server Database Size and Free Disk Space Daily Report

Powershell Scripts for DBA - SQL Server DBA

Powershell to HTML - SQL Server DBA