The cable that Motorola "will" be selling is a micro-usb to standard usb (female) adapter. I'll look around for the sources, but I know that I saw this detailed in a few videos from Motorola techs and at least one reviewer site.
The cable is not just a straight conversion of form factor, but has the necessary pinout to instruct the Xoom to jump into usb host mode. From the reports that I saw, it would absolutely provide the ability to plug usb drives, cameras and other accessories into the xoom for direct access. In fact, the reviewer that I saw, made a comment that they had verified the functionality by plugging in a couple usb keyboards... supposedly worked like a champ.
Lame that we need a "special" adapter, but I can imagine that if it is just a pinout issue, it may be possible for the good folks here at XDA to craft a software solution that would allow us to bypass the motorola-branded cable... It would have been WAY cooler if it was a standard usb port, like some of the other tablets on the horizon.