Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
intern_sql
Starting Member
3 Posts |
 Posted - 2009-01-12 : 10:34:29
|
| Greetings,I am new to sqlteam and I have a bear of a task at hand. I am at intern at this company and I am trying to simplify a tedious process for our finance department. I am trying to automate the process of 1.extracting an XML file from e-mail2. running a compare query with its contents against a table in SQL server3. Returning the results of the query to the administrators.In essence, any transaction ID from the XML that is not listed in the table should be compiled in a mail to he administrators. The other topics in this forum have be very helpful. ThanksAD |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-12 : 10:36:26
|
| 1.use xml datatype and functions query(),value(),...to create derived table from xml2.just a join with table will do3. use select with join and derived table |
 |
|
|
intern_sql
Starting Member
3 Posts |
Posted - 2009-01-12 : 10:44:48
|
quote:
Originally posted by visakh16
1.use xml datatype and functions query(),value(),...to create derived table from xml2.just a join with table will do3. use select with join and derived table
Thank you. I see you have a wealth of knowledge that you have shared with members of this forum. Should I try and use web services to automate the extraction of the file from e-mail? The e-mail is sent from the administrators of one of our partners to my supervisor. I will suggest that we set up a mail box that will allow me to manipulate the mail and attachments. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-12 : 10:52:19
|
| why is the xml being sent by email..then you have to create another process to extract the xml attachment or are you going to extract it manually. Just have a shared folder and tell them to save the xml there..then have another process pick it up from the shared folder to maybe another staging area...my 2 cents worth of limited experience. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-12 : 11:19:22
|
quote:
Originally posted by intern_sql
quote:
Originally posted by visakh16
1.use xml datatype and functions query(),value(),...to create derived table from xml2.just a join with table will do3. use select with join and derived table
Thank you. I see you have a wealth of knowledge that you have shared with members of this forum. Should I try and use web services to automate the extraction of the file from e-mail? The e-mail is sent from the administrators of one of our partners to my supervisor. I will suggest that we set up a mail box that will allow me to manipulate the mail and attachments.
just save file at some location from mail, then use some methods like export import to export data from file to your table. then do extraction and join part |
|
|
|
intern_sql
Starting Member
3 Posts |
Posted - 2009-01-13 : 09:03:20
|
| Thank you Visakh; I continue to work toward resolving this issue. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 09:11:19
|
| welcomelet us know if you face any more issues |
|
|
|
|
|
|
Extract XML attachmnet from mail and compare