April 30, 2013 at 7:43 am
Hi,
I',m new to XML and here is what I need to do...
I want to create a SQL query to pull data and update a field on a table with current date and another field an "X"
I need the data in the select to be written to an XML.
I then need the XML file emailed,
Once emailed I need to run another query to update the 'X" to 'Y' showing its been sent...
I assume I can do this from some job steps?
I really don't know where to start??
Thanks
Joe
April 30, 2013 at 8:09 am
jbalbo (4/30/2013)
...I need the data in the select to be written to an XML. ...
select ... FOR XML http://msdn.microsoft.com/en-us/library/ms178107%28v=sql.105%29.aspx
jbalbo (4/30/2013)
...I then need the XML file emailed, ...
exec sp_send_dbmail http://msdn.microsoft.com/en-us/library/ms190307.aspx%5B/url%5D
jbalbo (4/30/2013)
...Once emailed I need to run another query to update the 'X" to 'Y' showing its been sent....
Post example data and XML file.
April 30, 2013 at 11:15 am
Thanks for the info E4D4...
Ok so after what you send I have this ...
Created an SP XMLTest...
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[XML_Test] Script Date: 04/30/2013 10:29:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[XML_Test]
-- Report Parameters
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
-- @StartDate set time to 00:00:00
--Set @StartDate = dbo.usi_fnDatePart(@StartDate)
-- @EndDate set time to 00:00:00 and add one day (To this date at midnight)
--Set @EndDate = dbo.usi_fnDatePart(@EndDate)+1
UPDATE dbo.USER_DEFINED_DATA
SET CAD100 = 'X'
WHERE (dbo.USER_DEFINED_DATA.EffDate = CONVERT(DATETIME, '2012-04-03 00:00:00', 102)) and ASSESSMENT_MONIKER = '00437C0817DB41FB9516F449084ACBF5'
SELECT dbo.ASSESSMENT.AbbrName, dbo.USER_DEFINED_DATA.Expdate, dbo.USER_DEFINED_DATA.CAD4, dbo.USER_DEFINED_DATA.CAD16,
dbo.USER_DEFINED_DATA.CAD100, dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER
FROM dbo.USER_DEFINED_DATA INNER JOIN
dbo.ASSESSMENT ON dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER = dbo.ASSESSMENT.OID
WHERE (dbo.USER_DEFINED_DATA.EffDate = CONVERT(DATETIME, '2012-04-03 00:00:00', 102))
for xml auto
I then created this ....
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dbmailprofile',
@recipients = 'jbalbo@myemail.org',
@query = 'EXEC test.dbo.XML_Test' ,
@subject = 'XML Test',
@attach_query_result_as_file = 1 ;
I get an email but its in a text file and looks nothing like the sp when I run it ???
looks like ....
ML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
0x440841006200620072004E0061006D0065004407450078007000640061007400650044044300410044003400440543004100440031003600440643004100440031003000300044124100530053004500530053004D0045004E0054005F004D004F004E0049004B0045005200440E640062006F002E00410053005300450053
00
0x373444373442373539414546344136423141393437363432430108020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020610E40400002000343834
36
0x39414546344136423141393437363432430108020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020310E40400000300317663020610E404000020
00
0x444537373444373442373539414546344136423141393437363432430108020310E4040000010034020610E404000020003438343630444537373444373442373539414546344136423141393437363432430108020610E4040000200034383436304445373734443734423735394145463441364231413934373634324301
08
0x65766573020410E4040000FF0057617272656E2069732063757272656E746C79206F6E2061204348494E532C207768696368207761732066696C6564206F6E20627920746865207363686F6F6C20666F7220686973206F6E676F696E6720747275616E63792069737375652E204865206973207363686564756C656420746F
20
Thanks
Joe
April 30, 2013 at 11:44 am
BTW:
I was just looking again and it outputs as a TXT file..
IS it the for XML statement ?
April 30, 2013 at 11:47 am
Have a read here regarding the options, particularly for @body_format:
April 30, 2013 at 1:04 pm
sorry for being really CONFUSED...
If I run The SP I get the xml in the results window
but running thru the email when I open I get different results??
April 30, 2013 at 2:15 pm
I've found attaching an XML file as an attachment via sp_send_dbmail very troublesome.
I did this with WhoIsActive which is probably a little more complicated than you need.
http://jonmorisissqlblog.blogspot.com/[/url]
Basically the way I figured it out was to create a stored procedure. Within the stored proc I run a select FOR xml path, but return it as NVARCHAR(max). Then I called this stored proc within the @query option of sp_send_dbmail.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '[Database Mail Profile]',
@subject = 'Who Is Active',
@body_format = 'TEXT',
@query = 'SET NOCOUNT ON; SET ANSI_WARNINGS OFF; DECLARE @bodyXMLreturn nvarchar(max) EXEC dbo.sp_WhoIsActiveXMLout @bodyXMLreturn OUTPUT select @bodyXMLreturn',
@execute_query_database = '[Database]',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'WhoIsActive.xml',
@query_result_header = 0,
@query_result_width = 32767,
@query_result_separator = '',
@exclude_query_output = 0,
@query_result_no_padding = 0,
@query_no_truncate=1;
HTH,
Jon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply