Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Dan Christoffersen 64 posts 119 karma points
    Oct 11, 2009 @ 15:39
    Dan Christoffersen
    0

    How to get a list of members by member property

    I am trying to get a list of members by a property (deliveryRef) set to the same value.

    Searching through the forum didn't help much - only old threads that kind of seems like too much work for a simple tast.

    My initial though was to query the database directly, but that isn't as simple as it might sound, beacouse members and member properties are not in the same table (where are member properties by the way)

    Does anyone have a great way to achieve this?

  • Masood Afzal 176 posts 522 karma points
    Oct 11, 2009 @ 15:47
    Masood Afzal
    3

                string sqlQuery = "SELECT m.nodeId AS '@id', m.Email AS '@email', CAST(x.xml AS XML) AS '*' FROM cmsMember AS m INNER JOIN cmsContentXml AS x ON m.nodeId = x.nodeId FOR XML PATH('member'), ROOT('members'), TYPE ";


                // database sql returns data in following format 
                <members>
                  <member id="1201" email="[email protected]">
                    <node id="1201" version="d9e77af8-a6bf-4231-8449-bf09ed3132bf" parentID="-1" level="1" writerID="0" nodeType="1162" template="0" sortOrder="2" createDate="2009-08-08T23:54:37" updateDate="2009-08-08T23:54:37" nodeName="auto 3" urlName="auto3" writerName="Administrator" nodeTypeAlias="RegsiteredMembers" path="-1,1201" loginName="auto3" email="[email protected]">
                      <data alias="title">abc</data>
                    </node>
                  </member>
                </members>

  • Dan Christoffersen 64 posts 119 karma points
    Oct 11, 2009 @ 19:20
    Dan Christoffersen
    0

    Thanks - this seems very usefull.

    What you are doing very much exseeds my knowledge of sql. Where do I put the where clause like: WHERE deliveryRef=43? Or do I have to iterate through all the nodes?

  • Masood Afzal 176 posts 522 karma points
    Oct 11, 2009 @ 20:24
    Masood Afzal
    0
    SELECT m.nodeId AS '@id', m.Email AS '@email', CAST(x.xml AS XML) AS '*' FROM cmsMember AS m 
    INNER JOIN cmsContentXml AS x ON m.nodeId = x.nodeId
    WHERE xml LIKE '%Glasgow%'
    FOR XML PATH('member'), ROOT('members'), TYPE
  • skiltz 501 posts 701 karma points
    Oct 11, 2009 @ 21:40
Please Sign in or register to post replies

Write your reply to:

Draft