By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @xml XML
SELECT @xml = '<R>
<I>A</I>
<I>B</I>
<I>C</I>
<J>X</J>
<J>Y</J>
<J>Z</J>
</R>'
SELECT
Tbl.Col.query('for $i in I return $i').value('.', 'nvarchar(max)'),
Tbl.Col.query('for $i in J return $i').value('.', 'nvarchar(max)')
FROM @xml.nodes('R') Tbl(Col);
| (No column name) | (No column name) |
|---|---|
| ABC | XYZ |