T-SQL bcp syntax problems when outputting XML

Question

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

Eric Porter · Accepted Answer · 2016-05-23 02:28:55Z

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

Thanks a lot @Eric Porter, this workaround(global temp and powershell script) is the best solution I could implement in the meantime to at least automate the whole process. It is strange though because typing the whole bcp command as printed above on the DOS prompt will work just fine - but wrap it in a stored procedure and all hell breaks loose. Thanks very much!

Collectives™ on Stack Overflow

T-SQL bcp syntax problems when outputting XML

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related