CODEWITHSUNDEEP
sql-serverxmlt-sql
Ceasar
Ceasar

Reputation: 47

T-SQL bcp syntax problems when outputting XML

I have the bcp command below from a stored procedure. The stored procedure is constructed as follows:

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout "' + @offersFilename + '" -w -T';

EXEC xp_cmdshell @sqlCmd;

When I try to run this, I get the bcp usage response, meaning there is some syntax error in my @sqlCmd.

Using PRINT @sqlCMD, I get the following printout. Could someone please advise where I am getting it wrong. Thanks.

bcp "select custID as ""@id"", 
        (
          SELECT familyDesc as ""familyDesc"",
               upc as ""upc"",
               itemDesc as ""itemDescription"",
               offerCode as ""offerCode"",
               offerDesc as ""offerDesc"",
               validFrom as ""validFrom"",
               validTo as ""validTo"",
               cast(round(price,2) as decimal(19,2)) as ""price"",
               imageURL as ""imageURL""
           FROM tblCustomerOffers t2 where t1.custID=t2.custID
           for XML PATH('Offer'), TYPE
        )
        from tblCustomerOffers t1
        group by custID
        FOR XML PATH('Customer'), ROOT ('Offers'), ELEMENTS"
queryout "D:\offers\customerOffers.xml" -w -T

Upvotes: 0

Views: 341

Answers (1)

Eric Porter
Eric Porter

Reputation: 36

I've come across similar issues to this at work.

One way to make debugging this easier would be to instead of using bcp's queryout command, create a global temp table to store the result of your query. Then export that single record using bcp's out command.

For example,

CREATE TABLE ##FooResults (result XML)
INSERT INTO ##FooResults
SELECT (
    SELECT Bar AS [Bar]
    FROM Foo
    FOR XML PATH('Foo')
)

DECLARE @ExportXmlCommand VARCHAR(4000) = 'bcp ##FooResults out "C:\foo.xml" -w -T'
EXEC xp_cmdshell @ExportXmlCommand

DROP TABLE ##FooResults

The advantage of this is that it allows you to take advantage of SSMS's syntax highlighting and provides more detailed results in the case of an error.

The disadvantage is that all of the XML is squashed into a single-line.

To deal with that, I have devised a workaround by running a Powershell script that will re-format the XML so that it's readable.

Param(
    [string]$infile,
    [string]$outfile,
    [int]$indent = 4
)

function Format-XML {
    Param(
        [xml]$xml,
        [int]$indent = 4
    )
    $StringWriter = New-Object System.IO.StringWriter 
    $XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter 
    $xmlWriter.Formatting = "indented"
    $xmlWriter.Indentation = $indent
    $xml.WriteContentTo($XmlWriter) 
    $XmlWriter.Flush() 
    $StringWriter.Flush() 
    Write-Output $StringWriter.ToString() 
}

$result = Format-XML ([System.Xml.XmlDocument](cat (Resolve-Path $infile))) $indent
$result | Set-Content "$outfile"

(adapted from https://blogs.msdn.microsoft.com/powershell/2008/01/18/format-xml/)

So putting it all together, the last few lines end up looking like this:

DECLARE @ExportXmlCommand VARCHAR(4000) = 'bcp ##FooResults out "C:\foo.temp.xml" -w -T'
DECLARE @FormatXmlCommand VARCHAR(4000) = 'PowerShell.exe -ExecutionPolicy Bypass -File "C:\Format-Xml.ps1" "C:\foo.temp.xml" "C:\foo.xml"'
DECLARE @DeleteTempXmlCommand VARCHAR(4000) = 'del "C:\foo.temp.xml"'

EXEC xp_cmdshell @ExportXmlCommand
EXEC xp_cmdshell @FormatXmlCommand
EXEC xp_cmdshell @DeleteTempXmlCommand

I hope this helps you find the cause of the error!

Eric

Upvotes: 2

Related Questions