Indeed, only on CTs with XML Document output, this Dynamic Template tab shows up.
The template developer can put an XSLT document in here and it will be used to transform the generated XML Document output (of this Component Presentation) on the Content Delivery side.
The Component Presentation Assembler, when asked to generate the CP for a Component with this particular CT, it will automatically transform whatever XML the CP generated (on the CM side while publishing) with the XSLT specified in this tab.
Note the 2 distinct phases: CP generated at publish time vs. XSLT applied on the Content Delivery.
This is another mechanism (too little used, if you ask me) to integrate back-end CM with front-end CD (baking vs frying) logic.
UPDATE: As use case, think about integrations that need some dynamic code only available at "request-time" on the Presentation Server, but you still want to keep Templating and the entire code logic available/maintained in the CM.
Think about a stock ticker for example. Your CP outputs the XML of such a CP (consider it the model of a very simplistic MVC). Then with XSLT (i.e. the View) you style this information at 'request-time' including values from external services, e.g. price information, weather, etc...