I don't think that there is much more information to give, so I'll ramble for a bit and hope that helps.
Our company purchased a configuration of Microsoft CRM to use with one of our key business processes. This is a heavily customised implementation of MS CRM, but the bit that I'm dealing with for this question (Resource Groups) is a native MS CRM feature - it has not been changed from the vanilla CRM functionality.
Resource Groups (as I understand it) are a grouping of one or more different entities within the application instance - a Resource Group can contain any combination of these different entities (e.g. team, user, equipment, etc.). For example, you may have a resource group consisting of three teams, one user, and two items of equipment.
For reasons I will not pretend to understand, the Resource Group's main information is stored in the ResourceGroupBase table and the members of that group are stored in ConstraintBasedGroupBase. These two tables have a one-to-one relationship and are joined on their respective IDs (ResourceGroupId and ConstraintBasedGroupId). I honestly have no understanding why they are two different tables, as ConstraintBasedGroupBase simply appears to be an extension of the ResourceGroupBase table (as far as I'm aware with my limited knowledge of the application).
ResourceGroupBase contains a field called 'Name', which gives the name of the Resource Group. ConstraintBasedGroupBase contains a filed called 'Constraints', which contains the members of the corresponding Resource Group, stored as xml. As you have already seen, it is not what you might have thought of as properly constructed xml, as the different member IDs are contained within the same element. You will also notice the absence of anything to identify what the IDs represent - are they a team, user, or item of equipment? As far as I understand, the only way you're going to find out which is by plugging the id into the different tables and see which one gives you a result.
I know that this all seems very odd, but to get a better understanding of why it-is-how-it-is, I'm afraid you'll have to ask Microsoft, 'cause neither I nor my vendor know, and neither of us have any experience of working with this particular functionality.
I don't have access to the database at the moment, but I'm pretty confident that I can give all of the fields of relevance in each of the tables:
ResourceGroupBase table
GUID / primary key = 'ResourceGroupId'
Resource Group name = 'Name'
ConstraintBasedGroupBase table
GUID / primary key = 'ConstraintBasedGroupId'
Field containing group members = 'Constraints'
TeamBase
GUID / primary key = 'TeamId'
Team name = 'Name'
Notes
* ResourceGroupBase and ConstraintBasedGroupBase have IDs with different names, but they contain the same values
* Records from ResourceGroupBase and ConstraintBasedGroupBase have a one-to-one relationship
* For my problem, Resource Groups contain only teams (no users or items of equipment)
I could condense the problem down to using a single table - ConstraintBasedGroupBase - to remove any confusion about the other tables involved. If we approach the problem from that perspective, then we only need to look at two fields: 'ConstraintBasedGroupId' and 'Constraints'. If I show the type values of these fields for one row, we might get (with some artificially added formatting to make it look better):
ConstraintBasedGroupId = '065bbcce-eb01-e003-a59b-5550569a00af'
Constraints = '<Constraints>
<Constraint>
<Expression>
<Body>
resource["Id"] == {005b7cce-de01-e011-a55b-0050569d00bf} ||
resource["Id"] == {3dcbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {3fcbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {49cbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {4ccbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {4dcbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {4ecbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {4fcbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {53cbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {55cbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {5acbc1d6-a3e1-df11-a501-00188b8b43b5} ||
resource["Id"] == {71cbc1d6-a3e1-df11-a501-00188b8b43b5}
</Body>
<Parameters><Parameter name="resource" />
</Parameters>
</Expression>
</Constraint>
</Constraints>'
My aim is to convert this single row into a table containing 12 rows as follows:
ConstraintBasedGroupId TeamId
'065bbcce-eb01-e003-a59b-5550569a00af' '005b7cce-de01-e011-a55b-0050569d00bf'
'065bbcce-eb01-e003-a59b-5550569a00af' '3dcbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '3fcbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '49cbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '4ccbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '4dcbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '4ecbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '4fcbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '53cbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '55cbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '5acbc1d6-a3e1-df11-a501-00188b8b43b5'
'065bbcce-eb01-e003-a59b-5550569a00af' '71cbc1d6-a3e1-df11-a501-00188b8b43b5'
The next row in the ConstraintBasedGroupBase table would then append more results to this table.
Once I have this table, it is trivial for me to get the Resource Group's name from ResourceGroupBase and the member team's name from TableBase, as I have the corresponding IDs for each table.
So, my problems might be expanded into the following steps:
1) Extract the XML from the table field (ConstraintBasedGroupBase.Constraints)
2) Extract the string value for the <body> element from the XML
3) Parse the string to extract the different team IDs
4) Insert those IDs into a temporary table, along with the corresponding value for ConstraintBasedGroupId
5) Move onto the next row and repeat until I've extracted IDs for all members of all ResourceGroups
6) Use my temp table to get the names of the Resource Groups and corresponding teams.
I can probably bumble my way through most of these steps, but at the moment I can't even achieve the first two to get me started.
Everything clear as mud? It certainly is for me! :pinch:
Stuart