The sunroof channel is connected to 4 drain hoses- 2 front and 2 rear. The fronts drain down through the a-pillar and out the bottom of the car just inboard of the seam between the front fender and door. If you pour water into the sunroof channel, it should almost immediately drain out onto the ground. Sounds like your hose is disconnected from the sunnroof. The entire sunroof cassette comes out in one piece and that's the only way to get at the hoses to reconnect.

Oh one other thought to try. What if you take a smaller piece of hose, say a fuel line size about 2 feet long, and feed it down into the opening in the sunroof channel. Feed it about a foot down, then pour some water into this hose. Wonder where it will come out. Maybe if your hose is disconnected, but still directly adjacent to the channel tube, this will straddle the disconnected area enough to test the theory.

Two caveats:
Be very careful when feeding piece of wire down the drain tubes. Sometimes if the hose is loose but still connected to the sunroof cassette, the end of the wire being forced in there will dislodge the hose.

Second and far more important- there are airbags all over these cars! My X5 has airbags in both a-pillars. forcing a wire in there and not knowing where it's going is a very risky thing to do. You may be poking holes in your airbags. If you can't see the wire, stop immediately.

__________________
'04 E53, 4.4 Sport
'97 E39 528i
'86 911 Carrera, track car
'96 BMW R1100R