🔮Query on XML Column Data (Export/Filter)

There are a few places in Slate where the data is saved to the XML column, such as on the [message] table - and the data hidden there might be useful, such as when using Slate Voice and wanting to know the Call Duration. Technolutions generally does not include these columns in the export/filter selectors.

Any time you query the [message] table, you're inviting yourself into some very slow queries, as it is one of the largest tables in a mature Slate instance. Other [xml] columns may be more pragmatic to query on - we're just using this as an example.

By trial and error, you can figure out what data is saved in the XML column, and then how to export just that data - or even to filter on it.

Step 1 - Figure Out What Data is There

Add some filters to narrow down your query results to something reasonable (especially important if you're on the message table - a created date filter of recent messages is a MUST). Then, add a subquery export with a type of formula, a blank literal export, and [xml] in the formula field:

This will generate some results to give you an idea of just what's in this column:

Each field in these XML structures have a pattern of label/value wrapped inside of an element: 

<p><k>label</k><v>value</v></p>

Pasting the XML into Notepad++, Visual Studio Code, or another tool with the ability to auto-format XML can make it much more readable.

And we'll be using the defined label to select which value we want to export.

Step 2 - Getting it into an Export

The code we'll be dropping into the formula (Microsoft source) is this:

[xml].value('(p[k = "label_goes_here"]/v)[1]', 'varchar(max)')

The function defines that we want to export the value, and then the first argument allows you to define which field by inserting its label, found inside of the <k></k>. The second argument defines the SQL data output, which will often be varchar(max).

In this example, we'll pull in duration of a Slate Voice call:

By dropping in this code into the formula field:

[xml].value('(p[k = "duration"]/v)[1]', 'varchar(max)')

To generate this type of output:

This is the same operation Technolutions is doing to display Slate Voice information on your Timelines, and you can use this to display that same information in Queries, Reports, or on other Dashboards.

It is rare that you will find essential information hidden inside of the hidden XML fields in Slate. But occasionally, vital information like Slate Voice call duration can't fit within the defined table schema, so the XML field becomes the destination. And advanced users will find unusual uses for XML data, too (such as writing an audit query on Rules, or analyzing source format definitions).

Bonus - If You Can Export It, You Can Filter With It

Of course, if you can export the data out, you can build filters with it, too:

Here are some examples of using XPath with some exist() filters here: Find Unmapped Prompt V... | ReSource

And this one is writing filters using the Rule query base's XML column: Find Rules Assigning I... | ReSource

While both of these examples use pure custom SQL, you can also apply just the filter elements as simple subquery-formula filters - in the same design as in the screenshots in this article.

Go on - play around!