Trying to export FOR XML procedure using BCP - getting files padded with spaces

• siggemannen

  SSCrazy

  Points: 2436

  More actions

  March 19, 2015 at 11:41 am

  #305944

  Hello,

  I have a procedure that generates some XML from a bunch of tables.

  Then i use BCP to export it to a file. This works just fine.

  Here's the sample code:

  CREATE TABLE dbo.t_test (i INT, z VARCHAR(30) COLLATE DATABASE_DEFAULT)

  INSERT INTO t_test (i, z)

  SELECT1, 'Test'

  GO

  IF OBJECT_ID('SPRC_EXPORT') IS NOT NULL

  DROP PROCEDURE SPRC_EXPORT

  GO

  CREATE PROCEDURE SPRC_EXPORT

  AS

  SELECT*

  FROMt_test

  FOR XML PATH('root')

  GO

  But recently, i wanted to add a test that calls this procedure. So, Test procedure uses INSERT / EXECUTE thingy to put XML data into a temp table to compare things.

  But then you get following error: "The FOR XML clause is not allowed in a INSERT statement."

  DECLARE@T TABLE (x XML)

  INSERT INTO @t (x)

  EXECSPRC_EXPORT -- crashes here

  So, i thought, fine, i'll wrap the FOR XML inside a sub-SQL:

  CREATE PROCEDURE SPRC_EXPORT

  AS

  SELECT(

  SELECT*

  FROMt_test

  FOR XML PATH('root')

  )

  And BCP call still works, BUT, now the file generated becomes 64kb instead of 1kb 🙂 When i look into the file, it displays same XML, but the string is right-padded with a LOT of spaces.

  I think the problem lies in how BCP uses SET FMTONLY, OR that the "type" of result somehow gets changed when i do the wrapping.

  Anyone knows why the file grows so much and how can this situation be "fixed"?

  BR,

  Sigge

• siggemannen

  SSCrazy

  Points: 2436

  More actions

  March 19, 2015 at 3:38 pm

  #1785234

  Ok, finally found a workaround 😎

  You do this first thing in your SP:

  -- IF 1 = 0 will trick FMTONLY to process the first SELECT and get correct "datatype" of your query and it won't interfere with real calls

  IF 1 = 0

  BEGIN

  SELECT 'ANYTHING'

  FROM anytable

  FOR XML PATH('')

  END

  And after this, you can SELECT your real data