XML Error on output..

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  May 7, 2013 at 8:18 am

  #277235

  With some help from the forum I got my job to run.. thanks...

  here is the job, I changed the query to an example that I knew ran and I wanted the output to look like

  JOB...

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name='DBMailProfile',

  @recipients = 'jbalbo@email.org',

  @query = 'SELECT ''White'' AS Color1,

  ''Blue'' AS Color2,

  ''Black'' AS Color3,

  ''Light'' AS ''Color4/@Special'',

  ''Green'' AS Color4,

  ''Red'' AS Color5

  FOR

  XML PATH(''Colors''),

  ROOT(''SampleXML'')',

  @body = 'The stored procedure finished successfully.',

  @subject = 'Automated Success Message',

  @query_result_header = 1,

  @exclude_query_output = 1,

  @append_query_error = 1,

  @attach_query_result_as_file = 1,

  @query_attachment_filename = 'qry.xml'

  THIS IS THE OUTPUT I GET.... in the job

  The XML page cannot be displayed

  Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

  --------------------------------------------------------------------------------

  Invalid at the top level of the document. Error processing resource 'file:///C:/Users/jbalbo/AppData/Local/Microsoft/window...

  XML_F52E2B61-18A1-11d1-B105-00805F49916B

  THIS IS THE OUTPUT IN A QUERY WINDOW

  <SampleXML>

  <Colors>

  <Color1>White</Color1>

  <Color2>Blue</Color2>

  <Color3>Black</Color3>

  <Color4 Special="Light">Green</Color4>

  <Color5>Red</Color5>

  </Colors>

  </SampleXML>

  Thanks

  Joe

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  May 7, 2013 at 9:17 am

  #1613045

  So I found out that I needed

  , TYPE on The @QUERY

  EXEC msdb.dbo.sp_send_dbmail

  @QUERY= 'SET NOCOUNT ON SELECT lname as last, fname as first FROM dbo.client where LName = ''wilson'' FOR XML PATH(''REFERRAL''), root(''CLUB''),TYPE',

  BUT its like memorization I have no idea what it does if anyone knows a good place to read up on this I'd appreciate it...

  Also

  @query_attachment_filename= 'ATTACHMENT.xml'

  can I make this current date? like 05062013Attachement.xml

  Thanks

  Joe

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 7, 2013 at 9:46 am

  #1613052

  for the filename stuff, somewhere abovee your code, you need to construct the filename:

  declare @attachmentfilename varchar(100)

  SET @attachmentfilename = REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')

  -- formats 05/07/2013 and removes slashes to 05072013

  + 'Attachement.xml'

  PRINT(@attachmentfilename)

  and then in your code at teh bottom, change one parameter to use the variable instead of the hardcoded name:

  @query_attachment_filename = @attachmentfilename

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  May 7, 2013 at 12:40 pm

  #1613118

  Thanks for all the Help,

  I do have more one question...

  Is there a command to write the same file to a drive?

  This is now working ... Thanks!!

  DECLARE @OUTPUT xml

  SET @OUTPUT = (SELECT lname as last, fname as first FROM DB.dbo.client where LName = 'wilson' FOR XML PATH('REFERRAL'), root('CLUB'))

  SELECT @OUTPUT

  declare @attachmentfilename varchar(100)

  SET @attachmentfilename = REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')

  --REPLACE(CONVERT(VARCHAR,GETDATE,101),'/','')

  +'Clubhouse.xml' -- formats 05/07/2013 and removes slashes to 05072013

  --+ 'Attachement.xml'

  EXEC msdb.dbo.sp_send_dbmail

  @QUERY= 'SET NOCOUNT ON SELECT lname as last, fname as first FROM DB.dbo.client where LName = ''wilson'' FOR XML PATH(''REFERRAL''), root(''CLUB''),TYPE'

  ,@recipients = 'jbalbo@email.org'

  ,@subject= 'ClubHouse Download'

  ,@body = 'Attached is the daily clubhouse file'

  ,@attach_query_result_as_file= 1

  ,@query_attachment_filename= @attachmentfilename

  ,@query_result_width= 32767

  ,@query_no_truncate= 1

  ,@append_query_error= 1

  ,@query_result_header= 1