Extracting and parsing XML from fields (CRM related)

Question

Post reply

Extracting and parsing XML from fields (CRM related)

phual

SSChasing Mays

Points: 659
More actions
January 4, 2012 at 4:58 am

#248391

I'm a relative novice when it comes to SQL, so please be gentle with me. 😛
I've been getting on reasonably well producing reports from our Microsoft CRM-based application, but have come across something I've no experience of before. CRM allows ResourceGroups to be created to which you can assign resources. In our case, we're using these ResourceGroups to create team groupings, which we can then use in reports without having to hard code the groupings.
The problem I have is the way in which CRM stores these groupings. I was expecting that the relationships would be defined in a table, (one group to many resources), but it appears that they are actually stored as XML within a field so the SQL would be:
SELECT ResourceGroupBase.*, ConstraintBasedGroupBase.*
FROM ResourceGroupBase INNER JOIN
ConstraintBasedGroupBase ON ResourceGroupBase.ResourceGroupId = ConstraintBasedGroupBase.ConstraintBasedGroupId
and the XML that describes members of the group is contained in the 'Constraint' fiels:
<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>
This XML contains the GUIDS / IDs I require to link to the TeamBase table and get my membership, but I've never come across a situation such as this and don't know how to proceed with my query.
Is it possible (using SQL) to make a query that returns the resource group and associated teams such as:
ResourceGroup Team
Group1 Team A
Group1 Team B
Group1 Team C
Group2 Team Y
Group3 Team Z
If so, how would I go about it? I'm guessing it would involve parsing the XML, but I have no experience of this or how / if it is possible?
Stuart

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

phual SSChasing Mays Points: 659 More actions · Answer 1

phual

SSChasing Mays

Points: 659

January 6, 2012 at 7:50 am

#1429633

Double post

phual SSChasing Mays Points: 659 More actions · Answer 2

I take it that the lack of response mean 'we don't know' 🙁

Before I give up, can someone tell me if it's possible to move this post into the T-SQL forum without having to cross-post (as a little more research has suggested to me that the solution might be T-SQL)?

Thanks

Stuart

Peter Tingle SSC Enthusiast Points: 141 More actions · Answer 3

Would it be possible to have a little more info.

You have two tables;

ResourceGroupBase = ResourceGroupId (the GUID and unique reference to this table) and some details about the ResourceGroupBase

ConstraintBasedGroupBase = ConstraintBasedGroupId (according to your sql this is the same as the ResourceGroupBase but I would have thought it would be the unique reference for this table)

I understand you want the Group each team is in but I don't understand where this information is in the tables.

I assume the xml given is an example out of one column in one of the tables (the ConstraintBasedGroupBase?).

Could you give us the schema's for the tables and a couple of examples from the relevant fields.

Thanks,

Peter

LutzM SSC Guru Points: 107049 More actions · Answer 4

The lack of responses might also be an indicator that we don't have enough information yet.

You're looking for a "resource group and associated teams" but neither is part of the xml structure you posted.

Also, the xml is not really in a ready to use format. Xml data is better presented using the xml code tag.

Your xml data would look like this:

<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>

Based on those data, what would be your expected result?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

phual SSChasing Mays Points: 659 More actions · Answer 5

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>