Convert your SQL PS variable results to XML… it’s easy!!

Posted on by admin

Here’s some PowerShell code use to create a PS variable results from a T-SQL query using the SQL  Server 2008 (or higher) SQLPS command “Invoke-SQLCmd”:

Import-Module SQLPSV2

$SQL1 = @”
Select * from SurfSpotTable
“@

$SQL1Query = Invoke-Sqlcmd -Server ‘MAX-PCWIN1’ -Database ‘Developer’ $SQL1Query

As you can see, with a few lines of code we got back some table information:

 

$SQL1Query

SpotID     SpotName            Location                      State
——       ——–                      ——–                            —–
     1          Wildernes               Aguadilla                     PR
     2         Aviones                   Carolina                       PR
     3         Surfers Beach        Aguadilla                     PR
     4         Jobos                        Isabela                         PR

There’s a couple of things to understand about this code:

1. You need to download either Chad’s Miller SQLPS module for SQL Server 2008 or my SQL Denali SQLPSv2 module version.

2. Need to use the Import-Module to load the SQL Server snapins.

3. Invoke-SQLCmd is using Windows Authentication to connect to the database.

Now, that we have our PS variable “$SQL1Query” then we use the Export-Clixml to export the information to a XML formatted file:

## Convert to XML
$SQL1Query | Export-Clixml -Path createxml.xml -NoClobber -Encoding ASCII

ii createxml.xml

Then, use an XML Editor such as SAPIEN Technologies PrimalXML 2009 to open the file and see the information.  The XML file will look like this:

<Objs Version=”1.1.0.1″ xmlns=”http://schemas.microsoft.com/powershell/2004/04″>
  <Obj RefId=”0″>
    <TN RefId=”0″>
      <T>System.Data.DataRow</T>
      <T>System.Object</T>
    </TN>
    <ToString>System.Data.DataRow</ToString>
    <Props>
      <I64 N=”SpotID”>1</I64>
      <S N=”SpotName”>Wildernes</S>
      <S N=”Location”>Aguadilla</S>
      <S N=”State”>PR</S>
    </Props>
  </Obj>
  <Obj RefId=”1″>
    <TNRef RefId=”0″ />
    <ToString>System.Data.DataRow</ToString>
    <Props>
      <I64 N=”SpotID”>2</I64>
      <S N=”SpotName”>Aviones</S>
      <S N=”Location”>Carolina</S>
      <S N=”State”>PR</S>
    </Props>
  </Obj>
  <Obj RefId=”2″>
    <TNRef RefId=”0″ />
    <ToString>System.Data.DataRow</ToString>
    <Props>
      <I64 N=”SpotID”>3</I64>
      <S N=”SpotName”>Surfers Beach</S>
      <S N=”Location”>Aguadilla</S>
      <S N=”State”>PR</S>
    </Props>
  </Obj>
  <Obj RefId=”3″>
    <TNRef RefId=”0″ />
    <ToString>System.Data.DataRow</ToString>
    <Props>
      <I64 N=”SpotID”>4</I64>
      <S N=”SpotName”>Jobos</S>
      <S N=”Location”>Isabela</S>
      <S N=”State”>PR</S>
    </Props>
  </Obj>
</Objs>

So, with a few lines of code we just produce an XML file from a SQL DataRow results.  You be the judge… but I think this is another example of PowerShell Awesomeness!!

.. I’m Just Saying!!

Stay tuned!! I will have more on PowerShell, SQL, and XML files soon.