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
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
@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?
