The field “FilterBytes” contains the subscription filters for batching. We are converting from 2010 to 2013, and I need the a report of the “source of truth” as to which of our customers goes to which party, so we build the same parties correctly in 2013.

<pre>
select top 1000 
  p1.Name as PartnerNameA, 
  p2.Name as PartnerNameB, 
  ag.Name as AgreementName,
  bd.OnewayAgreementId,
  bd.Name as BatchDescriptionName,
  ag.PartnershipId,
  --bd.ReleaseCriteriaType,
  --bd.MessageCount,
  --bd.MessageScope,
  --bd.InterchangeSize,
  --bd.RecurrenceType,
  --bd.FilterBytes,
  --Convert(varchar(max), bd.FilterBytes) as 'ConvertFilterBytes',
  replace(replace(replace(replace(replace(replace(replace(CONVERT(VARCHAR(max), FilterBytes),'
','') ,'',''), '',''),'',''),'',''),'',''),'','') as FilterBytesStripped,
  p1.PartnerId as P1_PartnerID,
  p2.PartnerId as P2_PartnerID,
  ps.PartnerAId ,
  ps.PartnerBId 
  from tpm.BatchDescription bd 
  inner join tpm.Agreement ag on ag.ReceiverOnewayAgreementId = bd.OnewayAgreementId 
  inner join tpm.Partnership ps on ps.PartnershipId = ag.PartnershipId 
  inner join tpm.Partner p1 on p1.PartnerId = ps.PartnerAId 
  inner join tpm.Partner p2 on p2.PartnerId = ps.PartnerBID 
  where bd.Protocol = 'x12' 
  order by p1.Name 
</pre>

The replace functions above remove some of the special characters (but not all of them). Some of those special characters are used as separators between keywords like filterOperator, filterValue, and property and the following value. Sorry if the replace characters don’t come through to the blog; not sure how to easily put them here.
FilterBytes is stored in hex, so you can use the convert to VARCHAR(max) to make it somewhat more readable.

You can take a sample value of FilterBytes and put in NotePad++.
If you split the FilterBytes on the at sign, it looks something like this:

<pre>
@FilterPredicateOhttp://schemas.datacontract.org/2004/07/Microsoft.BizTalk.B2B.PartnerManagement i)http://www.w3.org/2001/XMLSchema-instance@groups@FilterGroup
@ statements
@FilterStatement
@filterOperator™Equals
@filterValue™?http://schemas.microsoft.com/BizTalk/EDI/X12/2006#X12_00501_210
@property™BTS.MessageType
@FilterStatement
@filterOperator™Equals@filterValue™123456
@property™5ABC.Client.Schemas.EDI210_5010.ST03_CustId
</pre>

Note: Some of the other special characters seem to prevent the find command from working in NotePad++ and even in Powershell. I never stopped to figure that out or research more.

I don’t have access to 2010 prod where I work, so I asked the BizTalk admin to run the above query then save to a CSV. I then wrote a Powershell to do the more difficult work of parsing the filterBytes there. I might be able to share that in the future.