T SQL Export to XML

Question

I'm using MS SQL Server 2016 SP2.

I have a table that contains two columns like this:

Filename                      XML
---------                     --------------------
c:\myfolder\test.xml          <?xml version blah blah blah 
c:\myfolder\test2.xml         <?xml version blah blah blah
c:\myfolder\test3.xml         <?xml version blah blah blah

Using SQL I want to export the XML in the XML column and create the file from the Filename column. I have tried using a cursor and BCP as shown below, but I keep getting the message:

Copy direction must be either 'in', 'out' or 'format'. usage: bcp {dbtable | query} {in | out | queryout | format} datafile

This is my SQL - where am I going wrong?

Thanks for any help.

  DECLARE @name VARCHAR(MAX)
  DECLARE db_cursor CURSOR FOR
      SELECT [Filename] FROM Instances

  OPEN db_cursor

  FETCH NEXT FROM db_cursor INTO @NAME

  WHILE @@FETCH_STATUS = 0
  BEGIN
      EXEC xp_cmdshell 'bcp -S xxxxxxx -d xxxxxxxxx "SELECT [XML] FROM Instances WHERE [Filename] = @NAME" -o @NAME -T -c -t -x'

      FETCH NEXT FROM db_cursor INTO @NAME
  END

 CLOSE db_cursor
 DEALLOCATE db_cursor

I feel like this would be easier with SSIS. You could use a For Each Loop on the dataset returned from your table, and easily the use a Data Flow Task to write out the text files to a flat file source with a variable as the path. — Thom A
– Thom A ♦, Commented Mar 8, 2019 at 9:12
Tried SSIS but it messes up the format of the XML as there is no output to XML out of the box — Michael
– Michael, Commented Mar 8, 2019 at 9:22
@Michael if you uxed FOR XML and a flat file target it won't mess up anything. As for your code, why are you using SQL to execute shell commands? Shell commands aren't SQL so they don't know anything about SQL variables. That @Name string is just a string — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 8, 2019 at 9:49

Alexander Volok · Accepted Answer · 2019-03-08 11:23:12Z

2

copy direction must be either 'in', 'out' or 'format'. usage: bcp {dbtable | query} {in | out | queryout | format} datafile

Because of:

Missing queryout
Source query to be stated just after BCP and other switches to be listed after the query
Additionaly, the original code has a wrong injection of @Name variable into a dynamic sql.

Adjusted xp_cmdshell command:

EXEC xp_cmdshell 'bcp "SELECT CAST([XML] as XML) FROM Instances WHERE [Filename] = '+@NAME+'"  queryout "'+@NAME+'" -S"xxxxxxx" -d"xxxxxxxxx" -c -T'

If the outgoing file expected to be in a UNICODE, this extra switch: -w can be added

edited Mar 8, 2019 at 11:23

answered Mar 8, 2019 at 9:43

Alexander Volok

5,9903 gold badges19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Panagiotis Kanavos Over a year ago

The significant change in this answer is concatenating @NAME instead of just including it in the shell command

Alexander Volok Over a year ago

@Michael, I have tested on my side and adjusted the answer, the query is to be the first after bcp and only then other switches

Alexander Volok Over a year ago

@PanagiotisKanavos, the reason was actually in the wrong order. The right and working one is: bcp.exe "query" queryout "C:\somewhere" -list_of_arguments

Michael Over a year ago

I changed order but now receive the error 'Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file'

Alexander Volok Over a year ago

@Michael, can be related to permissions of the SQL Service account. Is it has enough permissions to read/write/create files? will this work: EXEC xp_cmdshell 'MKDIR "C:\Somewhere"' where somewhere is your full path to a place of xml files to be stored

|

Collectives™ on Stack Overflow

T SQL Export to XML

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related