This is a work of art. BioShock 2 is an outstanding game with an incredible storyline, great message, and intuitive gameplay. That being said, it is definitely NOT appropriate for younger children.
The language can be an issue if you are against cursing. I found it terrifying to play near my parents as I was constantly afraid a curse would blare out of the speakers. They can be avoided by turning off the dialogue, but then you miss some of the deep storyline. The violence is also brutal, but to be expected in an FPS.
As for whether or not it's appropriate for your child, that really is up to you. I find the reviewers here are far too conservative, and the "Not for Kids" rating this game received is much too harsh. The sexual content is almost non-existent, and the cursing mainly comes from the enemies that angrily barrel at you. This is not to say the language isn't all over, it's just not as bad as many think it to be. The game teaches moral values and the importance of family and love, and the player influences the ending in a big way, depending on his or her actions. Unfortunately, many children will not be able to experience this amazing title due to the massive amounts of violence and cursing. In the end, it all depends on your child's maturity level. Talk to them about it, and make your own decision. They are, after all, your kids. I feel you know them better than I do.