question

technette avatar image
technette asked

How do I parse the xml part of a database mail to a table?

I have created a database mail solution. The data portion of each email is stored as xml in the msdb table. How do I write a stored procedure that will parse the data to a table. I need this feature to enable the auditors to view the archieved items. SQL 2005
sql-server-2005databasedatabase-mail
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@technette Can you post the abridged version of the xml? It is pretty easy to write queries against xml data, but the sample is needed.
0 Likes 0 ·
technette avatar image technette commented ·
The body is actually HTML format like the following.... TO: Someone lastname ( Manager) CC: someone2 lastname ( Manager) someone3 lastname ( Manager) ------------------------------------------------------------- Our blah blah blah blah blah indicate : -- is defined as a Lunching status employee -- has not worked Blah blah blah -- but has stuff Employee ID: 66666 Employee Name: Emp Number1 Last Work Date: 01/01/1999 Last Work Job-WBS: 99999-09999 Some Manager: please coordinate with the listed Other2 Manager and Other3 Manager above to appropriately re-assign this employee assets listed: Number Description PPP99999 /The best computer on the market/ OOOOOLL66 /The best monitory on the market/ Reference Slot: TARTQ2
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@technette Sent email messages are kept in the dbo.sysmail_mailitems table in msdb database. The **body** column of this table is of nvarchar(max) data type. This is where the HTML of the messages are stored. What do you mean exactly when you say that you need to parse the data to a table? What do you need to parse and what are the columns and data types of the destination table?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.