This question motivated some 30 (at least) years of measure theory. I will start with some informal discussion which I find to be instructive.
Negative answers. The question can be rephrased by asking when two probability spaces are strictly isomorphic. The answer is almost never, even if they are bijective.
The point is that strict isomorphism (= (bi-)measurable bijection) is too strong a notion. The correct notion is rather isomorphism $\rm mod~0$, (see [A] Dfn. 9.2.1(ii)). The point is to allow for the existence of $\mu_i$-negligible sets $N_1$ to be removed from the spaces $X_i$ and then ask for the existence of a strict isomorphism between $X_1\setminus N_1$ and $X_2\setminus N_2$.
Even in this case the answer is typically negative.
Coming to the additional condition, I suppose you mean: for every $\epsilon>0$ and every $U\subset X$ there exists $U'$ with $U'\subset U$ and $0<\mu U'<\epsilon$. (If otherwise the condition is void: choose $U'=\varnothing$).
This amounts to say that $\mu$ has no atoms. Even in this case, the answer is in general negative (even for isomorphisms $\rm mod~0$).
The serious obstruction is essentially how large the space $X_i$ is, but not in the sense of cardinality of the set $X_i$, rather, in terms of the cardinality of a family of subsets generating the $\sigma$-algebra $\Sigma_i$.
So far for disappointments. Now some affirmative answers.
Affirmative answers (under further assumptions). For most practical purposes in analysis and probability, if $\Sigma_i$ is "small" and "well fitting", then the answer should be yes if and only if the measures are both atomless (it is otherwise clear that atoms should be matched one by one, and this is possible only if all atoms with the same mass appear in both spaces and in the same cardinalities).
"Small" means countably generated: there exists a countable family $\mathcal A_i\subset \Sigma_i$ such that $\Sigma_i=\sigma(\mathcal A_i)$, the smallest $\sigma$-algebra containing $\mathcal A_i$.
"Well fitting" means separating, i.e. for all $x, y\in X_i$ there exists $A\in \Sigma_i$ with $x\in A$ and $y \notin A$.
The first accessible affirmative result is [A] Thm. 9.2.2. Recall that a probability space $(X,\Sigma,\mu)$ is a Borel probability space if there exists a distance $d$ on $X$ such that: (1) $(X,d)$ is a complete and separable metric space, and (2) $\Sigma$ is the Borel $\sigma$-algebra induced by the topology generated by $d$.
Let $(X,\Sigma,\mu)$ be a Borel probability space. Then it is
isomorphic $\rm mod~0$ to $([0,1],\nu)$ for some Borel probability
$\nu$ on $[0,1]$. If $\mu$ is atomless, then $\nu$ can be chosen as
the Lebesgue measure on $[0,1]$.
By transitivity, all atomless Borel probability spaces are isomorphic $\rm mod~0$.
This is an instance of a (much more) sophisticated result known as Maharam Theorem.
You can see that $\Sigma$ is countably generated by choosing a countable dense set $D:=(x_i)_i$ in $X$ (existence by separability) and noting that the topology is generated (in the sense of topologies) by the countable family of subsets $\{ B_r(x_i): x_i\in D, r \in \mathbb Q^+\}$, hence the Borel $\sigma$-algebra is generated (in the sense of $\sigma$-algebras) by the same family.
Furthermore, $\Sigma$ is separating because it contains singletons.
References. A good starting reference is [A] §9.2, which can be read without too much knowledge of measure theory on topological spaces. In general §§9.2–9.5 provide a good introduction to the subject (and some counterexamples).
A complete understanding of Maharam Theorem (to its full extent) requires a lot of material (some buzz-words: measure algebras, Maharam type, homogeneity, etc.) that is covered in volume 3 of [B] (it is considered a postgraduate reference).
[A] V. I. Bogachev, Measure Theory
[B] D. H. Fremlin, Measure Theory
