I don't use XDCAM, so this is based on my understanding.
You shouldn't need to convert the footage for use in either Premiere Pro cs6 or After Effects cs6. Sony XDCAM is natively handled in Premiere Pro CS6. But, it's more awkward using After Effects because unlike Premiere Pro CS6, it doesn't have a media browser that knows how to logically handle the xdcam folder structure and handle spanning. You would need to traverse the folder hierarchy to the elementary mp4 files. Do you have Premiere Pro CS6? If you do, the easiest thing to do is use the Premiere Pro CS6 media browser to import the media into a Premiere Pro project media bin. From there, you can copy and paste the clips from Premiere Pro CS6 into After Effects CS6 or save the Premiere Pro project (you don't need to create any sequences) and import that into your After Effects cS6 project. Once you do that, the media will be in your AE project media bin ready for use.