It's sort of straight-forward to see why \sqrt{|det(g)|} comes up in 2-dimensions, but I guess the generalization to more dimensions requires the mathematics of forms.
It might be worth looking at how it works in 2 spacelike dimensions. There, you can think of an integral over all space as the limit of a sum, where the sum is over little rectangles with sides \vec{\delta x} and \vec{\delta y}. The issue is: how to compute the area of the rectangle (in 3-D, it would be computing the volume of a parallelepiped, in 4-D, it would be computing the hypervolume of some 4-dimensional cell). In Cartesian coordinates, the displacement vectors \vec{\delta x} and \vec{\delta y} are orthogonal, and the lengths are just |\delta x| and |\delta y|, respectively, so you just use the usual formula for area of a rectangle: A = |\delta x| |\delta y|. But if you're using curvilinear coordinates, the two displacements are not necessarily orthogonal. In that case, how do you compute the area?
Well, here's a heuristic argument: In good-old Euclidean space, the area of a parallelogram with sides \vec{U} and \vec{V} is given by:
A = \vec{U} \times \vec{V} = |U||V| sin(\theta) where \theta is the angle between the two sides. We also know that:
\vec{U} \cdot \vec{V} = |U||V| cos(\theta)
So we can relate the cross product to the dot product as follows:
|\vec{U} \times \vec{V}|^2 = |U|^2 |V|^2 sin^2(\theta) = |U|^2 |V|^2 - |U|^2 |V|^2 cos^2(\theta) = (\vec{U} \cdot \vec{U}) (\vec{V} \cdot \vec{V}) - (\vec{U} \cdot \vec{V})^2
The dot-product can be written in terms of the metric:
\vec{U} \cdot \vec{V} = g_{\mu \nu} U^\mu V^\nu
So we can rewrite the cross-product in terms of the metric:
|\vec{U} \times \vec{V}|^2 = (g_{\mu \nu} g_{\alpha \beta} - g_{\mu \alpha} g_{\nu \beta}) U^\mu U^\nu V^\alpha V^\beta
Now, let's specialize to the case \vec{U} = \vec{\delta x} and \vec{V} = \vec{\delta y}. In that case, U^x = \delta x, U^y = 0, V^x = 0, V^y = \delta y. So we have:
|\vec{\delta x} \times \vec{\delta y}|^2 = (g_{x x} g_{y y} - g_{x y} g_{x y}) \delta x^2 \delta y^2 (All other terms are zero)
Note that the expression involving the metric is just the determinate of the 2x2 matrix:
\left( \begin{array} \\ g_{x x} & g_{x y} \\ g_{y x} & g_{y y} \end{array} \right)
So,
|\vec{\delta x} \times \vec{\delta y}|^2 = det(g) \delta x^2 \delta y^2
So,
|\vec{\delta x} \times \vec{\delta y}| = \sqrt{det(g)} \delta x \delta y
It would take a lot more work to see that this generalizes to more than 2 dimensions and to the case of a pseudo-Euclidean metric, but it gives you a little bit of the flavor for why something like \sqrt{det(g)} might show up.