XML Path Function Delimiters for ProLaw Data in Cascades

One of my favorite methods of extracting cascade information from ProLaw is using the XML PATH() function in SQL. The XML Path function can extract multiple rows of data and transpose the data into a list in a single column.

Inside the code below, the highlighted yellow text is how I have been using this function for the last several years. The yellow subselect looks at all the contacts assigned to a matter and returns all the results that match oppos in their contact title. The yellow function places a comma, highlighted in orange, between each of the returned results.

The second sub select function highlighted in green, I've used CHAR(13), also highlighted in orange, but note the difference of comma to char(13). This will now put the list of data from the cascade in a list each item on a new line. This line return is a recent discovery for me.

So what? We can use use the XML Path function to fix the issue of exporting to excel and not assigning the correct column and rows. If you use subreports, and export to excel, it will never format properly, or at least I've never seen it work properly. By moving the complexity to the SQL data set, we can fix some of the export issues in SSRS and get our cascade custom data out to excel in the correct excel format.

* I used these two sub selects because they also have a difference where one is distinct, and the other is not. You can use distinct if you have duplicate data. I know the second function does not have duplicates in the data set, so I've removed the overhead of running the distinct sub routine. Just a time saver for the CPU.

SELECT matters.matters as 'Matters_PK', matters.ShortDesc, OpenedDate, matters.ClientSort,

(select stuff((select DISTINCT(contacts.FullName +' @ '+ COMPANY.COMPNAME) + ' , ' from matters m5 left outer join MattersContacts on m5.Matters = MattersContacts.Matters and MattersContacts.MtoCClass like 'Oppos%' left outer join CONTACTS on matterscontacts.Contacts = contacts.CONTACTS left outer join COMPANY on contacts.COMPANY = company.COMPANY where matters.matters = m5.Matters FOR XML PATH('')) ,1,0,'' ))'OpposingParty',

(select stuff((select MattersQLITIGATION2.QCOA +' : '+ MattersQLITIGATION2.QVIOLATION + CHAR(13) from matters m2 left outer join MattersQLITIGATION on MattersQLITIGATION.Matters = m2.matters left outer join MattersQLITIGATION2 on MattersQLITIGATION2.MattersQLITIGATION = MattersQLITIGATION.MattersQLITIGATION where matters.matters = m2.Matters FOR XML PATH('')) ,1,0,'' )) as 'COA',