Windows opens zip files by default, just the way it opens folders. (To the user's viewpoint, anyway - it's actually unzipping the zip file and showing you what's in it.) Pictures in there should show in your default viewer for that kind of file (jpg, png, etc.) Videos, the same thing.
SMS might be tricky, since they're normally backed up to the cloud, unless you have a specific SMS backup app that backs them up to the PC, then it depends on the format used for the backup. If it's an xml file, you can read it - if you take your time to figure out how to ignore the xml tags. It could be almost any format, though, including one that's not human-readable (IOW, you can't read it without running it back through the same app that created it [which is restoring the SMSs to the phone].)
Anything you want to be able to read, see, etc., on the PC, without searching for special programs to do it, you should just copy to the PC. Make a 'phone' folder, make 'picture', 'video', etc., subfolders and copy the files into them, by hand. Then you don't have to worry about zips, formats or anything else.
BTW, Titanium stores a lot of files as .tar.gz. You'll need something like 7zip to open those.
