Avatar

Level 3

Hello All,

I'm using LC 8.2 and the Database is SQLServer.  My requirement is:  I need to send email from a process to all members of a particular user group.  As far as i know there is no functionality in workbench which helps me in doing that.  So i'm taking email ids from the database.

Here is my Query:

SELECT   ',' + prin.email
FROM  edcprincipalentity AS prin,
      edcprincipalgrpctmntenti AS gr
WHERE  prin.id = gr.refchildprincipalid
     AND refparentprincipalid =   (SELECT id FROM edcprincipalentity lcuser
   WHERE lcuser.principaltype = 'GROUP'
   AND lcuser.commonname = '<GroupName>')

And the output will be like this:    

,abc@xyz.com

,pqr@lmn.com

,xyz@cde.com

I'm using 'Query for Multiple Rows as XML', to query the database.  Now I need to convert it to a plane string containing email ids inorder to use in Email control.

I'm using SetValue's execute control to convert that.  I have a string variable to which i'll be putting the string valueof the xml using serializable.

/process_data/@EmailIds = serialize(/process_data/EmailIdsFromDB)

This is giving me the string EmailIds = ",abc@xyz.com ,pqr@lmn.com ,xyz@cde.com"

Now my problem is i cannot use the result directly on my Email control as it contains an extra comma.  I have to somehow remove that one.

/process_data/@EmailIds = substring(/process_data/@EmailIds, 2)

But I'm not getting the desired result.

Can any one help me out or suggest me some other method of doing this?

Thanks

Deepak