Solved

SQL Server 2005, Email XML File

Posted on 2007-03-29
24
1,646 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
ID: 18818542
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
ID: 18819623
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
ID: 18819944
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:Fraser_Admin
ID: 18820696
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
ID: 18820731
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
ID: 18820873
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
ID: 18820882
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
ID: 18820893
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
ID: 18820959
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
ID: 18827223
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
ID: 18827416
>>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
ID: 18827421
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
 

Author Comment

by:Fraser_Admin
ID: 18827491
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
ID: 18827494
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
ID: 18827504
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
ID: 18827513
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
ID: 18828467
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
ID: 18828472
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
ID: 18828863
>>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
ID: 18828954
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
ID: 18829031
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
ID: 18829103
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
ID: 18830905
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
ID: 18831837
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

861 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