I know it's possible because of the existence of APIs and applications that do this.
As far as I can tell, however, the relevant APIs are undocumented.
How can I read previously sent and received SMS messages via iCloud?
InflatableDonkey is an open source project that downloads iCloud backups.
SMS messages are stored in target/backups/<icloud_username>/<hash>/<date_str>/HomeDomain/Library/SMS/sms.db
, which is an SQLite3 database.