Solved

SQL Server 2005, Email XML File

Posted on 2007-03-29
24
1,631 Views
Last Modified: 2010-05-18
I have created an xml document using the For Explicit.  I would now like to email it using the sp_send_dbmail comment and attach it as an xml file.

How do I do this?
0
Comment
Question by:Fraser_Admin
  • 12
  • 10
  • 2
24 Comments
 

Expert Comment

by:jrosengr
Comment Utility
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mail Profile',
    @recipients = 'johan.rosengren@AAA.se',
    @query = 'SELECT COUNT(*) FROM [DATABASE].dbo.[TABLE]' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'Test.xml' ;
0
 

Author Comment

by:Fraser_Admin
Comment Utility
The problem is, how to i get the results of the for xml to be an actual xml file.

I can run the query and generate the xml data fine, but i need to automatically save the results to a file so i can automatically email it.

Hope this makes sense.
0
 

Expert Comment

by:jrosengr
Comment Utility
This example sends an e-mail message Database Mail attaches the result as a text file
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mail Profile',
    @recipients = 'johan.rosengren@AAA.se',
    @query = 'SELECT * FROM [DATABASE].dbo.[TABLE] FOR XML AUTO' ,
    @subject = 'XML File',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'Test.xml' ;
0
 

Author Comment

by:Fraser_Admin
Comment Utility
My query is very cumbersome.  There is nothing that you can doing after the select to tell it to save to a file?
0
 

Author Comment

by:Fraser_Admin
Comment Utility
Don't think this works.  This is what I get in Test.xml:

XML_F52E2B61-18A1-11d1-B105-00805F49916B                                                                                                                                                                                                                        
                                                                                                                                                                                                                                                               
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
0x44094D00610073007400650072005F00490044004410500072006F0064007500630074005F00470072006F00750070005F00490044004419500072006F0064007500630074005F00470072006F00750070005F004400650073006300720069007000740069006F006E00440F500072006F0064007500630074005F0046006F
0072006D005F00490044004413500072006F0064007500630074005F00430061007400650067006F00720079005F00490044004420500072006F0064007500630074005F00470072006F00750070005F004400650073006300720069007000740069006F006E005F004600720065006E00630068004412500072006F00640075
00
0x44005300540002024226000000020311280052006100770020004D006100740065007200690061006C007300200050006F0077006400650072000204420B0000000205420C0000000206112A004D0061007400E9007200690061007500780020004200720075007400200050006F007500640072006500020711060052004D
00500043010902011108004500440053005400020242270000000203112600480069002D0047007200610064006500200054007200650065006C0065006E0067007400680002044208000000020542030000000206112800480069002D0047007200610064006500200054007200650065006C0065006E006700740068002000
02

(2 rows affected)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Since For Xml does not return a resultset, but rather a stream, the only way you could do this in the past (with SQL Server 2000) was using some application to retrieve the stream.  SQL Server 2005 has added some enhancements and now supports a new keyword (TYPE) in the FOR XML clause.  Perhaps you can use that to return the Xml as an Xml data type and so send it in an email.  But I have never done it that way (and probably would not consider doing it that way).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Incidentally, the reason you are getting it returned in segments of 256 bytes has to do with the setting for the  @query_no_truncate flag.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
It looks like your question was answered in BOL.  See here:
SQL 2005 sp_send_dbmail with XML in body or as attachment outputs HEX not XML
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=633800&SiteId=1
0
 

Author Comment

by:Fraser_Admin
Comment Utility
This is the output i'm getting now.  i need this to make an actual xml file.  why does it have the rowcount at the end?        
                                                                                                                                                  <scales_fpnb.dbo.Lookup_Product_Group Master_ID="BG" Product_Group_ID="-1" Product_Group_Description="Not In List" Product_Form_ID="-1" Product_Category_ID="-1" Product_Group_Description_French="Non Dans La Liste" Product_Group_Code="NIL"/><scales_fpnb.dbo


(1 rows affected)


This is the call I'm using:
EXEC msdb.dbo.sp_send_dbmail
    @Profile_name = 'DBMailProfile',
    @recipients = 'myEmail@xxxx.com',
    @query = 'SELECT * FROM [scales_fpnb].dbo.[Lookup_Product_Group] FOR XML AUTO, TYPE' ,
    @subject = 'XML File',
    @attach_query_result_as_file = 1,
    @query_result_header = 0,
    @query_attachment_filename = 'Test.xml' ;
0
 

Author Comment

by:Fraser_Admin
Comment Utility
ok i added set nocount on, set nocount off to my query, and now i don't see the number of rows affected at the end.

here is the last part of my problem....

how do i make it line break at the end of a row??

right now it is just making one very big line, so it won't let me open it as an xml file.  

Here is a copy of my file generated....

<root><scales_fpnb.dbo.lookup_product_group><product_group_id>44</product_group_id><product_group_Description>Pulp 4 Ft. (1.22M) Roundwood</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_grou
p_id>58</product_group_id><product_group_Description>Pulp 5 Ft. (1.52M) Roundwood</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>5</product_group_id><product_group_Description>Pulp
8 Ft. (2.50M) Roundwood</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>35</product_group_id><product_group_Description>Pulp Green Chips</product_group_Description></scales_fpnb.dbo.
lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>43</product_group_id><product_group_Description>Pulp Mixed Length Roundwood</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group
><product_group_id>7</product_group_id><product_group_Description>Pulp Semi-Treelength</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>28</product_group_id><product_group_Description
>Pulp Small Chips</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>8</product_group_id><product_group_Description>Pulp Treelength</product_group_Description></scales_fpnb.dbo.lookup_p
roduct_group></root>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>how do i make it line break at the end of a row??<<
Xml files do not have rows and therefore no line breaks.

>>right now it is just making one very big line, so it won't let me open it as an xml file.  <<
Try opening it with IE or some other Xml aware editor.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I just did and this is what it looks like:

<root>
      <scales_fpnb.dbo.lookup_product_group>
            <product_group_id>44</product_group_id>
            <product_group_Description>Pulp 4 Ft. (1.22M) Roundwood</product_group_Description>
      </scales_fpnb.dbo.lookup_product_group>
      <scales_fpnb.dbo.lookup_product_group>
            <product_group_id>58</product_group_id>
            <product_group_Description>Pulp 5 Ft. (1.52M) Roundwood</product_group_Description>
      </scales_fpnb.dbo.lookup_product_group>
      <scales_fpnb.dbo.lookup_product_group>
            <product_group_id>5</product_group_id>
            <product_group_Description>Pulp 8 Ft. (2.50M) Roundwood</product_group_Description>
      </scales_fpnb.dbo.lookup_product_group>
      <scales_fpnb.dbo.lookup_product_group>
            <product_group_id>35</product_group_id>
            <product_group_Description>Pulp Green Chips</product_group_Description>
      </scales_fpnb.dbo.lookup_product_group>
      <scales_fpnb.dbo.lookup_product_group>
            <product_group_id>43</product_group_id>
            <product_group_Description>Pulp Mixed Length Roundwood</product_group_Description>
      </scales_fpnb.dbo.lookup_product_group>
      <scales_fpnb.dbo.lookup_product_group>
            <product_group_id>7</product_group_id>
            <product_group_Description>Pulp Semi-Treelength</product_group_Description>
      </scales_fpnb.dbo.lookup_product_group>
      <scales_fpnb.dbo.lookup_product_group>
            <product_group_id>28</product_group_id>
            <product_group_Description>Pulp Small Chips</product_group_Description>
      </scales_fpnb.dbo.lookup_product_group>
      <scales_fpnb.dbo.lookup_product_group>
            <product_group_id>8</product_group_id>
            <product_group_Description>Pulp Treelength</product_group_Description>
      </scales_fpnb.dbo.lookup_product_group>
</root>
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Fraser_Admin
Comment Utility
when i double click on the xml file, it gives an ie error message.

if i go in and actually break it up the way you are displaying it, then it will open in ie for me.

so why does it not work when it is all pushed together?
0
 

Author Comment

by:Fraser_Admin
Comment Utility
This is the error I get...

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


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

A name was started with an invalid character. Error processing resource
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
This has to be after you email it, right? If so make sure to use @body_format = HTML
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Never mind I see that you are sending the Xml as an attachment so that parameter si probably not going to help.  But in any case, please confirm the format you are using and if the problem only occurs when you receive it via email.  What happens when you try the query from SMSS?
0
 

Author Comment

by:Fraser_Admin
Comment Utility
When I run the query, it says mail queued.  This is the query i'm running...

EXEC msdb.dbo.sp_send_dbmail
    @Profile_name = 'DBMailProfile',
    @recipients = @emailaddy
    @query = 'set nocount on; SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_product_group] where product_group_description like ''%pulp%'' for xml Auto, TYPE, root(''root''), elements set nocount off;',
    @subject = 'XML File',
    @attach_query_result_as_file = 1,
    @query_result_header = 0,
    @query_no_truncate = 1,
    @query_attachment_filename = 'Test.xml' ;

The problem occurs when I try to open the xml file from the email.  So, if I edit the file, I see that the file does not have a new line for each tag, sometimes the line breaks in the middle of a tag.   If I edit the file and put the line breaks after each tag, manually, then it opens as a normal xml file.
0
 

Author Comment

by:Fraser_Admin
Comment Utility
I've read some different posts about ansi/unicode.  Not sure if that is part of my problem, since I don't really understand what the difference is, or how it applies to what i'm doing.  But there seems to be others that are having problems that the email cannot be sent as ansi.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>When I run the query, it says mail queued. <<
I meant what happens when you run the quey on its own as in:
SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_product_group] where product_group_description like '%pulp%' for xml Auto, TYPE, root('root'), elements
0
 

Author Comment

by:Fraser_Admin
Comment Utility
I get a normal xml file.  Well actually i first get a clickable resultset, and when i click on it, it opens an xml file.  formatted properly.

<root>
  <scales_fpnb.dbo.lookup_product_group>
    <product_group_id>44</product_group_id>
    <product_group_Description>Pulp 4 Ft. (1.22M) Roundwood</product_group_Description>
  </scales_fpnb.dbo.lookup_product_group>
  <scales_fpnb.dbo.lookup_product_group>
    <product_group_id>58</product_group_id>
    <product_group_Description>Pulp 5 Ft. (1.52M) Roundwood</product_group_Description>
  </scales_fpnb.dbo.lookup_product_group>
  <scales_fpnb.dbo.lookup_product_group>
    <product_group_id>5</product_group_id>
    <product_group_Description>Pulp 8 Ft. (2.50M) Roundwood</product_group_Description>
  </scales_fpnb.dbo.lookup_product_group>
  <scales_fpnb.dbo.lookup_product_group>
    <product_group_id>35</product_group_id>
    <product_group_Description>Pulp Green Chips</product_group_Description>
  </scales_fpnb.dbo.lookup_product_group>
  <scales_fpnb.dbo.lookup_product_group>
    <product_group_id>43</product_group_id>
    <product_group_Description>Pulp Mixed Length Roundwood</product_group_Description>
  </scales_fpnb.dbo.lookup_product_group>
  <scales_fpnb.dbo.lookup_product_group>
    <product_group_id>7</product_group_id>
    <product_group_Description>Pulp Semi-Treelength</product_group_Description>
  </scales_fpnb.dbo.lookup_product_group>
  <scales_fpnb.dbo.lookup_product_group>
    <product_group_id>28</product_group_id>
    <product_group_Description>Pulp Small Chips</product_group_Description>
  </scales_fpnb.dbo.lookup_product_group>
  <scales_fpnb.dbo.lookup_product_group>
    <product_group_id>8</product_group_id>
    <product_group_Description>Pulp Treelength</product_group_Description>
  </scales_fpnb.dbo.lookup_product_group>
</root>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Ok.  That means the problem occurs only when you email it using sp_send_dbmail.  Perhaps you need to set @query_result_width to some value bigger than the default 256.

But as I have never used sp_send_dbmail to send mail and/or attachments, I really don't know.

I would also try something like this:
Declare @SQL varchar(MAX)

Set @SQL = 'SELECT CAST((SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_product_group] where product_group_description like ''%pulp%'' for xml Auto, TYPE, root(''root''), elements) AS VARCHAR(MAX))'

EXEC msdb.dbo.sp_send_dbmail
    @Profile_name = 'DBMailProfile',
    @recipients = @emailaddy
    @query = @SQL,
    @subject = 'XML File',
    @attach_query_result_as_file = 1,
    @query_result_header = 0,
    @query_no_truncate = 1,
    @query_attachment_filename = 'Test.xml' ;
0
 

Author Comment

by:Fraser_Admin
Comment Utility
Here is my call.....

Declare @SQL varchar(MAX)

Set @SQL = 'set nocount on; SELECT CAST((SELECT product_group_id, product_group_Description from scales_fpnb.dbo.[lookup_product_group] where product_group_description like ''%pulp%'' for xml Auto, TYPE, root(''root''), elements) AS VARCHAR(MAX)); set nocount off;'

EXEC msdb.dbo.sp_send_dbmail
    @Profile_name = 'DBMailProfile',
    @recipients = @emailaddy,
    @query = @sql,
    @query_result_width = 20000,
    @subject = 'XML File',
    @attach_query_result_as_file = 1,
    @query_result_header = 0,
    @query_no_truncate = 1,
    @query_attachment_filename = 'Test.xml',
    @body_format = HTML;


Here is my xml file when I edit it after receiving it in notepad...
<root><scales_fpnb.dbo.lookup_product_group><product_group_id>44</product_group_id><product_group_Description>Pulp 4 Ft. (1.22M) Roundwood</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>58</product_group_id><product_group_Description>Pulp 5 Ft. (1.52M) Roundwood</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>5</product_group_id><product_group_Description>Pulp 8 Ft. (2.50M) Roundwood</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>35</product_group_id><product_group_Description>Pulp Green Chips</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>43</product_group_id><product_group_Description>Pulp Mixed Length Roundwood</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>7</product_group_id><product_group_Description>Pulp Semi-Treelength</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>28</product_group_id><product_group_Description>Pulp Small Chips</product_group_Description></scales_fpnb.dbo.lookup_product_group><scales_fpnb.dbo.lookup_product_group><product_group_id>8</product_group_id><product_group_Description>Pulp Treelength</product_group_Description></scales_fpnb.dbo.lookup_product_group></root>

still not formatted properly.


this is the error:
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


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

A name was started with an invalid character. Error processing resource
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
I am afraid since i do not use sp_send_dbmail to send files, I have no idea why this is happening or even a workaround.

Good luck.
0
 

Author Comment

by:Fraser_Admin
Comment Utility
this is the work around i am using.  i am creating my xml file using bcp.  i am calling my select from a sp, so the bcp command is actually just calling a sp.  i am  calling the bcp by using the xp_cmdshell.  i am then emailing the file created.  i have posted other questions concerning how to get bcp working properly, if anyone else has the same problem.

i hope this helps someone else since i spent quite a bit of time getting this working, and to me, this should have been something that worked easily.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now