Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL query to XML ??? Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 7:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:00 PM
Points: 295, Visits: 488
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
Post #1448034
Posted Tuesday, April 30, 2013 8:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
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
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.
Post #1448050
Posted Tuesday, April 30, 2013 11:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:00 PM
Points: 295, Visits: 488
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
Post #1448155
Posted Tuesday, April 30, 2013 11:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:00 PM
Points: 295, Visits: 488
BTW:

I was just looking again and it outputs as a TXT file..

IS it the for XML statement ?
Post #1448166
Posted Tuesday, April 30, 2013 11:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
Have a read here regarding the options, particularly for @body_format:

http://msdn.microsoft.com/en-us/library/ms190307.aspx



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448169
Posted Tuesday, April 30, 2013 1:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:00 PM
Points: 295, Visits: 488
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??

Post #1448204
Posted Tuesday, April 30, 2013 2:15 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:19 AM
Points: 293, Visits: 813
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/

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
@recipients='[email address]',
@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
Post #1448232
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse