xml output from sql query

  • The script runs but gives me "no data to export"  I see data in the $query, but seems like not in:

    if ($items.Count -gt 0)

    THanks.

    # Clear the console window.
    Clear-Host

    # Database variables.
    $server = "srv1"
    $database = "ItemCost"

    # SQL query to select all data in the person table.
    $query = "SELECT * FROM [dbo].[item_dim] where itemid = '11-371517-582-3-02' ORDER BY [itemid]"

    # Export path and file.
    $exportPath = "C:\Demo\"
    $exportXml = "Itemexport.xml"

    # Check to see if the file path exists.
    if (Test-Path $exportPath)
    {



    # Extract the data.
    $items = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop
    try
    {


    # If data has been returned, do the export.
    if ($items.Count -gt 0)
    {

    # Construct the full file path.
    $fullFilePath = $exportPath + $exportXml

    # Create the XML file.
    $xmlWriter = New-Object System.XMl.XmlTextWriter($fullFilePath,$Null)

    # Set the formatting for the document.
    $xmlWriter.Formatting = 'Indented'
    $xmlWriter.Indentation = 1
    $XmlWriter.IndentChar = "`t"

    # Add the declaration for the document.
    $xmlWriter.WriteStartDocument()

    # Add the root element.
    $xmlWriter.WriteStartElement('items')

    # Process the rows of data.
    foreach ($item in $items)
    {

    # Open the person element.
    $xmlWriter.WriteStartElement('item')

    # Add the person details.
    $xmlWriter.WriteElementString('itemid', $item.itemid)
    $xmlWriter.WriteElementString('itemdesc', $item.itemdesc)
    $xmlWriter.WriteElementString('itempline', $item.itempline)
    $xmlWriter.WriteElementString('itemplinedesc', $item.itemplinedesc)
    $xmlWriter.WriteElementString('itemcategory', $item.itemcategory)

    # Close the person element.
    $xmlWriter.WriteEndElement()

    }

    # Close the root XML element.
    $xmlWriter.WriteEndDocument()

    # Flush the internal buffer.
    $xmlWriter.Flush()

    # Close the XML document.
    $xmlWriter.Close()

    # Today's date.
    $today = Get-Date

    # Construct the backup file name.
    $exportBackupXml = $exportXml.Substring(0, $exportXml.Length-4) + "-" + `
    [int]$today.DayOfWeek + "-" + `
    $today.DayOfWeek.ToString().ToLower() + ".xml"

    # Check if the backup file does not exist, or if it does, check that
    # today's date is different from the last modified date.
    if (-not (Test-Path ($exportPath + $exportBackupXml)) -or `
    ((Test-Path ($exportPath + $exportBackupXml)) -and `
    ((Get-Item ($exportPath + $exportBackupXml)).LastWriteTime.date -ne $today.Date)))
    {

    # Copy the XML export.
    Copy-Item ($exportPath + $exportXml) `
    -Destination ($exportPath + $exportBackupXml) -Force

    }

    # Message stating export successful.
    Write-Host "Data export successful."

    }
    else
    {

    # Message stating no data to export.
    Write-Host "There is no data to export."

    }

    }
    catch
    {

    # Message stating export unsuccessful.
    Write-Host "Data export unsuccessful."

    }

    }
    else
    {

    # Message stating file path does not exist.
    Write-Host "File path does not exist."

    }
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Try $items.Rows.Count


    I'm on LinkedIn

  • Tried that but seems like the query from SQL returns no records, but if I use that query in SSMS data returns.

    ?

     

    Thanks.

  • have you tried adding a debug to it

    $items = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop

    $items |out-file "c:\sql.log"

    and also try

    $items = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop -As DataSet

    $items |out-file "c:\sql.log"

     

  • I have the one record captured in $items |out-file "c:\sql.log"

    Debug:

    $items.rows.Count

    # If data has been returned, do the export.

    if ($items.rows.Count -gt 0)

    $items.rows.Count   shows 0

    Message:

    0

    There is no data to export.

    Thanks.

     

  • This was removed by the editor as SPAM

  • Anything else I can do for Debug...

  • Does the code look correct?

    THanks.

  • anything else I can try to see why script not working?

    THx

  • What do you get if you have Write-Host $items after the invoke-sql ? Also, change the "SELECT *" to use the actual columns that you need.

    Another thing you could try is something like

    foreach ($item in $items)

    { $item.<columnname>  etc.}

    ..just to see if anything is actually getting put into the $items object.

    So a debug script might look something like

    $server = "YOUR SERVER"
    $database = "YOUR DATABASE"

    # SQL query to select all data in the person table.
    $query = "SELECT !!!!COLUMN NAMES!!!! FROM [dbo].[item_dim] where itemid = '11-371517-582-3-02' ORDER BY [itemid]"

    $items = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop

    Write-Host $items

    foreach ($item in $items)

    {Write-Host $item.<column> ..... other columns
    }


    I'm on LinkedIn

  • This debug script is returning data as expected.

    Thanks.

  • Any other things I can try to figure out why script isn't working?

    Thanks.

  • any ideas why script not working?

     

    Thx.

  • Bruin wrote:

    any ideas why script not working?

    Thx.

     

    What is the output of the debug script I provided above?


    I'm on LinkedIn

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply