asked on

Using BCP to export to XML, SQL 2008R2

I've been trying for days now to get an xml file output from SQL2008R2.
Today I stumbled upon this suggestion and I think it could work but I'm getting errors.
I'll list the code below and then the errors. I'm hoping for some insight so that I can resolve the errors and make it work..

USE server
GO

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
GO

RECONFIGURE
GO

EXEC SP_CONFIGURE 'XP_CMDSHELL', 1
GO

RECONFIGURE
GO

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 0
GO

RECONFIGURE
GO

DECLARE @FILENAME VARCHAR(50)
DECLARE @SQLCMD VARCHAR(500)

SELECT @FILENAME = 'E:\TEST\TEST.XML'

SELECT @SQLCMD = 'BCP ' +
                        '"SELECT test ' +
                        ' FROM server ' +
                        ' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
                        ' QUERYOUT ' +
                        @FILENAME +
                        ' -W -T -S ' + @@SERVERNAME

EXECUTE MASTER..XP_CMDSHELL @SQLCMD

errors:
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53].
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
NULL

Ioannis Paraskevopoulos

Can you provide sample data and the format of XML you want to output?

Giannis

Mark Wills

Well, a couple of things...

I think you need a lowercase "w" as param (which is Unicode, prefer -c instead of -w) and normally include output file in quotes, and don't have a space before the server name.

ie the server name [-Sserver_name[\instance_name]]

Your server\instance name should be per the @@servername but you will need to triple check. e,g, if local server then use -S.\instance

Note the PRINT command - have a look in message tab to make sure it is formatted correctly

DECLARE @FILENAME VARCHAR(50)
DECLARE @SQLCMD VARCHAR(500)

SELECT @FILENAME = 'E:\TEST\TEST.XML'

SELECT @SQLCMD = 'BCP ' + 
                        '"SELECT test ' + 
                        ' FROM dbname.schema.tablename ' +
                        ' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
                        ' QUERYOUT "' +
                        @FILENAME +
                        '" -w -T -S' + @@SERVERNAME

PRINT @SQLCMD

EXECUTE MASTER..XP_CMDSHELL @SQLCMD

Open in new window

rgolenor

ASKER

mark, I had a lowercase 'w' but I set everything to uppercase when I posted just to make it look cleaner.

I made some of your suggested changes and the print command gives this result:

BCP "SELECT testtable FROM dbname FOR XML PATH (''), ROOT('ORDERS'), TYPE " QUERYOUT "E:\TEST\TEST.XML" -c -T -Sservername

I'm still getting the errors.

Giannis, I'm not sure what formats are available for XML.. Here are the columns from the table i'm trying to export however:

DEP, DOC_ALIAS, DOC_NO, SCHEDULED_DATE, ORDER_DATE, CUST_PO, ITEM_CODE, NOTE, SHIPPED, ITEM_QTY, QTY_SHIPPED, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY,
SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, DELIV_METH, OPS_NOTES, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY, CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgolenor

ASKER

Thanks so much Mark! I read and re-read your post(s) and after some tinkering I finally
got it to work.. I guess I just needed to understand the structure a little better as some little tweaks were all that was needed. So here is the actual code that worked:

DECLARE @FILENAME VARCHAR(50)
DECLARE @SQLCMD VARCHAR(500)

SELECT @FILENAME = 'E:\TEST\TEST.XML'

SELECT @SQLCMD = 'BCP ' +
                        '"SELECT * ' +
                        ' FROM everest_ics.dbo.A3_AGILITY ' +
                        ' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
                        ' QUERYOUT ' +
                        @FILENAME +
                        ' -c -T '

EXECUTE MASTER..XP_CMDSHELL @SQLCMD

Results:
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)
NULL

rgolenor

ASKER

Thanks again Mark!

Mark Wills

Great to see you have it working. Always happy to help :)