Use T-SQL to Parse Dynamics CRM Form XML

It's possible to use SQL Server (including SQL Azure) to parse the XML for CRM forms. With the form's XML, you can run a query to, for example, list all JavaScript libraries applied to a form.

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(max)
SET @XmlDocument = N'YOUR CRM FORM XML GOES HERE'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT *
  FROM OPENXML (@DocHandle, '/form/formLibraries/Library',1)
      WITH (name varchar(100),
            libraryUniqueId varchar(50))
EXEC sp_xml_removedocument @DocHandle

Or list all tabs on the form, as in this example.

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(max)
SET @XmlDocument = N'YOUR CRM FORM XML GOES HERE'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT *
  FROM OPENXML (@DocHandle, '/form/tabs/tab',1)
      WITH (name varchar(100))
EXEC sp_xml_removedocument @DocHandle

Benefits of copying Dynamics CRM Online metadata to SQL

Dynamics CRM is, to a large extent, a metadata-driven platform, as illustrated here. What this means is that a lot of its functionality is derived by business entities, their relationships, fields, picklists, etc.

Given the importance of metadata in CRM, you'd expect that you'd be able to write a query against it, such as "List all account fields that are not on a form." or "List all web resources and the forms, if any, each one is used on.". Unfortunately, these types of queries aren't possible without writing a fair bit of SDK or Web API code.

Being a long time user of SQL Server and T-SQL, my instinct was to find a way to regularly copy CRM Online's metadata to SQL tables. To follow is an overview of what I have working so far.

My initial set of requirements are these:

• Make it quick and easy to run robust queries (joins, filters, sorting) against CRM Online metadata.
• Allow for enhancing CRM's metadata, such as listing all picklist items next to each picklist (OptionSet) field and showing whether a field is on any forms and which ones.
• Allow for diffs on the metadata, to be able to determine, for example, what new entities and fields were created in the past week.
• Provide a fast on-demand way to export CRM Online metadata to an Excel file.
• Allow for the service to run for multiple CRM Online organizations.

The approach I've taken (so far) is this:

• An Azure WebJob is responsible for querying CRM Online metadata (entities, fields, relationships, roles, global optionsets, views, forms and form structure, etc.) and copying this data to a set of SQL Server tables. Entity Framework, along with some CRM SDK code, makes this relatively easy to do. (The benefit of using a WebJob is that it can be scheduled or triggered on-demand.)
• A SQL Azure database stores the latest metadata and maintains copies of the previously extracted metadata. Stored procedures write differences to a table for easy reporting.
• Another WebJob (I might move it to REST service) allows for extraction of the metadata into various forms, such as Excel and Word templates. This allows for fast documentation of a CRM Online's org metadata.

This initial design has met all of my requirements except for handling multiple CRM orgs. That will take a bit more work and will probably deplete my Azure credits each month so I'm leaving it as a "nice to have".

I'll hopefully be able to put this out as open source but until then I hope you can glean some ideas from this information. And... hopefully Microsoft will provide the same types of functionality soon so that I don't need to maintain this solution. Although I have to admit, it was fun to build.

Using Microsoft Azure with Dynamics CRM

I've always been uncomfortable with the word "can't", especially when it comes to software. My manager at Onyx Software around 2002, John Hawk, once told his team that it's "just a matter of moving data from here to there" and that has always stuck with me. Sure, it can be difficult to move lots of data quickly, transform it for different systems, analyze it for meaning, present it clearly, etc. but there's usually a way to do all of those things. "Can't" should not be allowed in the room.

Every experienced Dynamics CRM user/admin/dev should spend some time each week answering questions on the Dynamics CRM forums. I try to answer a few questions each week. I especially like the questions or responses that use that word -- can't. My immediate response it, "oh yeah, let's see about that".

One cure for the Dynamics CRM can't's is Microsoft Azure. Azure means "world of possibilities"... in some language, I'm sure of it. These cloud services open up a myriad of possibilities for any company or organization to further automate the business, provide better customer service, make better decisions and lower costs.

So far, as I write this, I've listed 43 ways that those responsible for Dynamics CRM in their company can improve CRM with Azure. I came up with a lot of them in response to that word in the CRM forums. "I can't schedule a job to run against CRM data." Yes, you can, with a scheduled WebJob or other scheduled service. "Without writing code, I can't create an Excel file, populate it from CRM data and store it on SharePoint." Yes, you can, with an Azure Logic App or Microsoft Flow.

There are already several ways to link CRM and Azure (Service Endpoints, WebHooks, REST, etc.) and this will continue to expand. There will come a day when CRM administrators will be seamlessly using Azure services without seeing that it's "Azure". Perhaps CRM's workflows can start an "extended workflow" (really an Azure Flow workflow) or a CRM form can interact with a Node.js app (running as an Azure Function). Whatever Microsoft comes up with, the can't's won't stand a chance.