2

I have a stored procedure that ends with FOR XML EXPLICIT and I need to put the xml file in a folder.

I've set up the following PowerShell, and run the script providing the two parameters.

The name of the script is xml.ps1

./xml "xml" "c:\test.xml"

param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=SLISQL;Database=Development_App;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "dbo._REX_NewSP"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
if ($outputType  -eq "Text") 
{
$DataSet.Tables[0] | format-table -auto > $filename
}

if ($outputType  -eq "xml") 
{
$DataSet.Tables[0] |Export-Clixml $filename
}

I see the xml output in the PowerShell screen, but no text.xml file is output.

1

I turned your PS into a function, just changing the calls, ran it through the PS ISE, and it worked just fine for me in testing, outputting the data to the location. I would ensure that you have the ability to write to the root of the C: drive. Typically this can require admin permissions. It's possible you are swallowing the error somewhere.

function ProcToXmlFile {
param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases for XML AUTO"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
if ($outputType  -eq "Text") 
{
$DataSet.Tables[0] | format-table -auto > $filename
}

if ($outputType  -eq "xml") 
{
$DataSet.Tables[0] |Export-Clixml $filename
}
}

ProcToXmlFile "xml" "c:\temp\test.xml"

Created the file in the correct location with the contents:

<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>
      <S N="XML_F52E2B61-18A1-11d1-B105-00805F49916B">&lt;sys.databases name="master"/&gt;&lt;sys.databases name="tempdb"/&gt;&lt;sys.databases name="model"/&gt;&lt;sys.databases name="msdb"/&gt;&lt;sys.databases name="HealthCheck"/&gt;&lt;sys.databases name="RestoreAttachTEST"/&gt;</S>
    </Props>
  </Obj>
</Objs>

Here's an updated version (a little hacky, I know) that writes the header to a file, and then just the XML data that is produced. See if this gets you a little closer.

function ProcToXmlFile {
param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost\sql16;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases for XML AUTO"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | Format-Wide -AutoSize
$Headerstring = '<?xml version="1.0" encoding="UTF-8?>'
$Headerstring | Out-File $filename
if ($outputType  -eq "Text") 
{
$DataSet.Tables[0] | Format-Table -HideTableHeaders | out-file $filename -Width 5000 -Append
}

if ($outputType  -eq "xml") 
{
$DataSet.Tables[0] |Export-Clixml $filename
}
}

ProcToXmlFile "text" "c:\temp\test.xml"
  • Thanks. I was able to get it to work. Being new to PowerShell took me awhile to figure out how to make the function work. Unfortunately the outputted xml is still not what I need. Powershell does some things to it that I have no explanation for at this point. Specifically, the insertion of &gt;lt between elements. I need two things. first, the insertion of the encoding <?xml version="1.0" encoding="UTF-8?>, which I think powershell can do for me, and second just the basic xml under it that gets created in the stored procedure in SQL using FOR XML EXPLICIT exported to a specific folder. – daveg Oct 13 '16 at 12:44
  • Does the updated PS get you closer @daveg? – Nic Oct 13 '16 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.