Email content copied into Item List field

0

Hi Andreia,

First, it would be much easier to implement what you need if you were able to have an Excel attachment instead of the table in email body. If that's not possible, do you have a fixed number of rows/columns in the table or is it variable?

Let me know.

Cheers,
Martin

Hi martin,

thank you for your answer! the excel file will not be possible because this is an email that will be generated from another app and the idea is to pick the information and start the process in webcon.
I do have a fixed number of columns but not a fixed number of rows because the email basically contains codes that need to be added into the inventory.

I've attached an example of the email.

Hope you have any ideas!
Thank you

Assuming you have a clean HTML table in your email body, you could use a query like the following one to retrieve data from it (run the query in SSMS to see the results):

/* SQL START */
DECLARE @xml XML =
'<table>
<tr>
<th>Col 1</th>
<th>Col 2</th>
<th>Col 3</th>
</tr>
<tr>
<td>Val 1a</td>
<td>Val 1b</td>
<td>Val 1c</td>
</tr>
<tr>
<td>Val 2a</td>
<td>Val 2b</td>
<td>Val 3c</td>
</tr>
<tr>
<td>Val 3a</td>
<td>Val 3b</td>
<td>Val 3c</td>
</tr>
</table>'

SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ColumnIndex
,td.value('(.//text())[1]','nvarchar(max)') AS tdText1
,td.value('(.//text())[2]','nvarchar(max)') AS tdText2
,td.value('(.//text())[3]','nvarchar(max)') AS tdText3
FROM @xml.nodes('//table/tr') A(td)
/* SQL END */

Does this help?

Best regards,
Martin

It's giving me errors.

Do you initialize the item list with this code?
Where do you use this code in order to translate the xml into the field?

Thank you!!

I didn't actually test this in Designer Studio, but you should be able to make it work.

1. Get the content of the email in a multiple lines of text field.
2. Grab the whole table from HTML using a business rule (BR1). Inside it place a SQL command and do something like SUBSTRING where the starting position is "<table>" and ending position is "</table>".
3. Create another business rule (BR2), again with SQL command and use the query I posted above. When declaring @xml = "BR1".
4. Use BR2 for item list initialization.

Try if that works. If it doesn't, can you paste the whole email HTML here and I can have a look at it?

Hi Martin,

I really new when it comes to those advanced queries, i've searched online for the substring and found an example:

SELECT SUBSTRING(REPLACE(col,'<p><strong>By ',''),0,CHARINDEX('<',REPLACE(col,'<p><strong>By ','')))
FROM (VALUES
('<p><strong>By Dr. Mercola</strong></p> <blockquote> <p>In an interview with ElectromagneticHealth.org'),
('<p><strong>By Barbara Loe Fisher</strong></p> <blockquote> <p>Here we are in the winter of 2015, and '),
('<p><strong>By Gary Ruskin<br> Co-Founder and Executive Director, U.S. Right to Know</strong></p> <blockquote> <p>U.S. Right to Know')
) as t (col)

Do you mean something like this?
Sorry to bother you with this but I'm with some doubts!

Thanks!

Hi Andrea,

there’s no one who can help you without knowing the raw content of the mail as it is stored inside the database. It may be that there are nested tables or that the table may only look like a table but doesn’t use the table element.

@Martin
Thanks, I wasn’t aware that you could use xpath selectors. :)

Best regards,
Daniel

Hi Andreia,

I tested my assumptions and it doesn't work as expected. The business rule returns only the first column of the query, semi-colon delimited. To work around this, you need to do some more steps. To recap, those are the steps to follow:

1. Save the content of the email in a multiple lines of text field
2. Use a business rule to retrieve the whole <table> tag from HTML
3. Create a SQL table that will hold the data from the HTML
4. Create a stored procedure to insert data into the table
5. Create an action to execute the stored procedure
6. Select data from table from point 3 to initialize the item list.

It would be too long to describe everything in detail here. If you need detailed instructions, feel free to reach out to me at martin@meze.si.

Cheers,
Martin