Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Why won't my results save to the xml path I specified in my stored procedure?

Here is my code:

 EXEC master..xp_cmdshell
               'bcp "SELECT title
                                   ,author
                                   ,location
                                   ,ISBN
 FROM    libraryInventory
 WHERE  inventory_level > 2            
 for xml auto, elements, root(''location'')
               "
               queryout \\main\libraries\locations\inventories.xml -x'

Instead of outputting my result to the above path, this is what I get as a result:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
NULL
What am I doing wrong?
0
programmher
Asked:
programmher
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the newlines from your sql...

EXEC master..xp_cmdshell  'bcp "SELECT title ,author,location,ISBN FROM    libraryInventory WHERE  inventory_level > 2   for xml auto, elements, root(''location'') " queryout "\\main\libraries\locations\inventories.xml" -x' 

Open in new window


you might consider moving the query into a view, and just use the view name (as table name) instead, this will shorten this code.

you could build the "sql" into a variable, if it's about readability

declare @cmd varchar(1000)
set @cmd = 'bcp "SELECT title ,author,location,ISBN '
set @cmd = @cmd + ' FROM    libraryInventory WHERE  inventory_level > 2 '
set @cmd = @cmd + ' for xml auto, elements, root(''location'') " '
set @cmd = @cmd + ' queryout "\\main\libraries\locations\inventories.xml" -x' 
exec(@cmd)

Open in new window


hope this helps
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now