Here’s a little SQL gymnastics used to parse and query against a predictably-formatted long text field.
The challenge: We have a single text field that contains a list person_id-to-area mappings, and we need the query the database for a list of those people with their names.
For example, this it the query used to fetch the mapping field:
select value -- a "text" field (e.g. not a varchar) from setting where setting_name = 'InstructorAreaMap'
And that query returns “
42|Accounting\r\n51|Marketing\r\n69|Strategy“. But what we want is a set of records that contains not only the Person ID and Area, but also the name of the person from the Person table, where Person ID is the key.
We can do this in a single query like so:
- Convert the text field into a varchar that we can use for replace operations.
- Replace the delimiters with xml tags.
- Convert the result into an XML data type.
- Select against that XML.
- Join in the Person table and select the result.
Step 2 builds an XML string that looks like “
<r><i>42</i><a>Marketing</a></r><r><i>51</i><a>Accounting</a></r><r><i>69</i><a>Strategy</a></r>“, and Step 4 essentially converts that into a joinable table that looks like this:
42 Marketing 51 Accounting 70 Strategy
After this it’s easy to get the result we need:
Accounting Fred Flintstone 51 Marketing George Jetson 42 Communication Barney Fife 70
Here’s the SQL query:
select assignments.area, p.display_name name, p.person_id from ( -- STEP 4: -- Create a table of person_id (i), area (a) pairs from an xml string -- in the form "<r><i>42</i><a>Area</a></r><r><i>..." called "assignments" select c.value('(i)', 'int') as person_id, c.value('(a)', 'varchar(50)') as area from ( -- STEP 3: -- select an XML type called "data" in the above form using -- information in the "value" field of the "InstructorAreaMap" setting select cast ( -- STEP 2: replace( '<r><i>' + replace( -- STEP 1: -- Convert the text field into something we can use SUBSTRING( convert( varchar(max), value ), 1, DATALENGTH(value) ), -- search char(13)+char(10), -- find '</a></r><r><i>' ) -- replace with + '</a></r>', -- outer serach '|', -- outer find '</i><a>' ) -- outer replace with as xml ) data from setting where setting_name = 'InstructorAreaMap' ) as t cross apply data.nodes('r') as t2(c) ) as assignments -- STEP 5: -- Join against the Person table to get the name. inner join person p on p.person_id = assignments.person_id order by area, name