How can I add my iCloud icon to my dock?

One method that works with the current MacOS Mojave and some earlier OS (but probably not Tiger where you posted):

Open a Finder window.

Click View menu > Show Path Bar.

Click Go menu > iCloud Drive.

Drag the iCloud Drive icon from the Path bar at the bottom of the Finder window to the right side of the Task Bar.

That is the expected behavior of folders in the Dock. iCloud Drive is a folder and when you have a folder in the Dock (similar to Downloads for example) when you click on it in the Dock it will do as you describe (as well as display any top level contents of the folder that you can click on directly without first clicking Open in Finder).