xml output from sql query

Question

Post reply

xml output from sql query

Bruin

SSCrazy

Points: 2058

August 4, 2021 at 1:53 am

Go to Answer

#3916721

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."

}

August 5, 2021 at 12:29 pm

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

PB_BI SSCoach Points: 17463 More actions · Answer 2

PB_BI

SSCoach

Points: 17463

August 5, 2021 at 9:39 am

#3917091

Try $items.Rows.Count

I'm on LinkedIn

Bruin SSCrazy Points: 2058 More actions · Answer 3

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

?

Thanks.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

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"

Bruin SSCrazy Points: 2058 More actions · Answer 5

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.

Bruin SSCrazy Points: 2058 More actions · Answer 6

Bruin

SSCrazy

Points: 2058

August 7, 2021 at 12:29 am

#3917631

Anything else I can do for Debug...

Bruin SSCrazy Points: 2058 More actions · Answer 7

Bruin

SSCrazy

Points: 2058

August 9, 2021 at 3:06 pm

#3918158

Does the code look correct?

THanks.

Bruin SSCrazy Points: 2058 More actions · Answer 8

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

THx

PB_BI SSCoach Points: 17463 More actions · Answer 9

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

Bruin SSCrazy Points: 2058 More actions · Answer 10

This debug script is returning data as expected.

Thanks.

Bruin SSCrazy Points: 2058 More actions · Answer 11

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

Thanks.

Bruin SSCrazy Points: 2058 More actions · Answer 12

Bruin

SSCrazy

Points: 2058

September 18, 2021 at 11:59 pm

#3930867

any ideas why script not working?

Thx.

PB_BI SSCoach Points: 17463 More actions · Answer 13

Bruin wrote:

any ideas why script not working?
Thx.

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

I'm on LinkedIn