creating an XML file with BCP

[PPettit]

I thought that I had figured out how to use BCP to create an XML file from a query in SQL 2005. Apparently, I was wrong. Now that I've added some additional code to better control the XML structure, it doesn't work.

When I run the following query (part of a much larger query)...

use databasename
go

-- --------------------------------------------------------
-- Enable xp_cmdshell
-- --------------------------------------------------------
-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- Update the currently configured value for advanced options.
RECONFIGURE
GO
-- Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Update the currently configured value for xp_cmdshell
RECONFIGURE
GO
-- Disallow further advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
-- Update the currently configured value for advanced options.
RECONFIGURE
GO
-- --------------------------------------------------------
-- Sample export via bcp
-- --------------------------------------------------------
DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd   VARCHAR(8000)
SELECT  @FileName = 'C:\SampleXMLOutput.xml'

SELECT  @SQLCmd = 'bcp "' +
'declare' +
	' @count as int,' +
	' @invoicetype as nvarchar(10),' +
	' @prefix as nvarchar(5),' +
	' @str as nvarchar(max),' +
	' @combinedtaxrate as decimal(8,5),' +
	' @taxexemptflag as nvarchar(10),' +
	' @taxlocationstate as nvarchar(5),' +
	' @totallineprice as decimal(8,5),' +
	' @xmloutput as xml,' +
	
	' @arparms_Uf_ARParmsContactName as nvarchar(75),' +
	' @arparms_Uf_ARParmsContactEmail as nvarchar(75),' +

	' @hdr_invoicenum as nvarchar(12),' +
	' @hdr_invoicedate as datetime,' +

	' @parms_company as nvarchar(60),' +
	' @parms_addr##1 as nvarchar(50),' +
	' @parms_addr##2 as nvarchar(50),' +
	' @parms_city as nvarchar(30),' +
	' @parms_state as nvarchar(5),' +
	' @parms_zip as nvarchar(10),' +
	' @parms_country as nvarchar(30),' +
	' @parms_phone as nvarchar(25),' +
	' @parms_Uf_ParmsDUNS as nvarchar(9)' +

' select' +
	' @hdr_invoicenum = ''      892872''' +

' select' +
	' @hdr_invoicedate = inv_date' +
' from databasename.dbo.inv_hdr' +
' where inv_hdr.inv_num = @hdr_invoicenum' +

' select' +
	' @parms_company = company,' +
	' @parms_addr##1 = addr##1,' +
	' @parms_addr##2 = addr##2,' +
	' @parms_city = city,' +
	' @parms_state = state,' +
	' @parms_zip = zip,' +
	' @parms_country = country,' +
	' @parms_phone = phone,' +
	' @parms_Uf_ParmsDUNS = Uf_ParmsDUNS' +
' from databasename.dbo.parms' +

' select' +
	' @arparms_Uf_ARParmsContactName = Uf_ARParmsContactName,' +
	' @arparms_Uf_ARParmsContactEmail = Uf_ARParmsContactEmail' +
' from databasename.dbo.arparms' +

' set	@prefix = ''pidx:''' +

' set @xmloutput = (' +
	' select' +
		' (select' +
			' ltrim(@hdr_invoicenum) as ''RP_Invoicenumber'',' +
			' convert(varchar(8),@hdr_invoicedate,112) as ''RP_InvoiceDate'',' +
			' (select' +
				' ''Vendor'' as ''RP_PartnerInformation/@partnerRoleIndicator'',' +
				' ''VendorDUNS'' as ''RP_PartnerInformation/RP_PartnerIdentifier/@partnerIdentifierIndicator'',' +
				' '''' as ''RP_PartnerInformation/RP_PartnerIdentifier'',' +
				' @parms_Uf_ParmsDUNS as ''RP_PartnerInformation/RP_PartnerIdentifier'',' +
				' @parms_company as ''RP_PartnerInformation/RP_PartnerName'',' +		
				' @parms_addr##2 as ''RP_PartnerInformation/RP_AddressInformation/RP_StreetName'',' +
				' @parms_city as ''RP_PartnerInformation/RP_AddressInformation/RP_CityName'',' +
				' @parms_state as ''RP_PartnerInformation/RP_AddressInformation/RP_StateProvince'',' +
				' @parms_zip as ''RP_PartnerInformation/RP_AddressInformation/RP_PostalCode'',' +
				' @parms_country as ''RP_PartnerInformation/RP_AddressInformation/RP_PostalCountry/RP_CountryCode'',' +
				' @arparms_Uf_ARParmsContactName as ''RP_PartnerInformation/RP_ContactInformation/RP_ContactName'',' +
				' @parms_phone as ''RP_PartnerInformation/RP_ContactInformation/RP_Telephone/RP_PhoneNumber'',' +
				' @arparms_Uf_ARParmsContactEmail as ''RP_PartnerInformation/RP_ContactInformation/RP_EmailAddress''' +
			' for xml path(''''),type)' +
		' for xml path(''''),type) as ''RP_InvoiceProperties''' +
	' for xml raw(''''),elements)' +

' select @str = cast(@xmloutput as nvarchar(max))' +

' select cast(''<pidx:Invoice transactionPurposeIndicator="Original" version="1.0" xmlns:pidx="[URL unfurl="true"]http://www.api.org/pidXML/v1.0">''[/URL] + replace(@str, ''RP_'', @prefix) + ''</pidx:Invoice>'' as xml)' +
'" queryout ' + @FileName +' -c -T -SServername'

-- display command, for visual  check
SELECT @SQLCmd AS 'Command to execute'

-- create the XML file
EXECUTE master..xp_cmdshell @SQLCmd

... an empty file is created and I get this message in the results window:

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Host-file columns may be skipped only when copying into the Server
NULL

What am I doing wrong? Is there something else I should be using instead of BCP?

Additional information: The query will probably be used as a stored procedure and run via an automated task.

 

[gmmastros]

Just a guess....

Try adding "SET NOCOUNT ON", like this:

SELECT  @SQLCmd = 'bcp "' +
'[!]SET NOCOUNT ON [/!]'+
'declare' +
    ' @count as int,' +
    ' @invoicetype as nvarchar(10),' +

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PPettit]

Thanks for the reply gmmastros, but it didn't change anything.

 

[PPettit]

In case anyone is interested, I figured out a solution:

I added this to the beginning of the "declare" section:

	' @test as nvarchar(max),' +

I changed this part:

' select cast(''<pidx:Invoice transactionPurposeIndicator="Original" version="1.0" xmlns:pidx="[URL unfurl="true"]http://www.api.org/pidXML/v1.0">''[/URL] + replace(@str, ''RP_'', @prefix) + ''</pidx:Invoice>'' as xml)' +
'" queryout ' + @FileName +' -c -T -SServername'

to this:

' select [b][red]@test =[/red][/b] cast(''<pidx:Invoice transactionPurposeIndicator="Original" version="1.0" xmlns:pidx="[URL unfurl="true"]http://www.api.org/pidXML/v1.0">''[/URL] + replace(@str, ''RP_'', @prefix) + ''</pidx:Invoice>'' as [b][red]nvarchar(max)[/red][/b])' +
[b][red]' select @test as XML' +[/red][/b]
'" queryout ' + @FileName +' -c -T -SServername'

Now, the file is created with no errors. However, the XML code comes out as one long string instead of being nicely formatted.