By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test(data xml);
insert into test values
('
<ROOT>
<Customer>
<Order>Order 1</Order>
</Customer>
<Customer>
<Order>Order 2</Order>
</Customer>
</ROOT>');
1 rows affected
SELECT data.value('(ROOT/Customer/Order)[1]', 'varchar(100)')
FROM test;
| (No column name) |
|---|
| Order 1 |
SELECT T2.lin.value('(Order)[1]', 'varchar(100)')
FROM test
CROSS APPLY data.nodes('/ROOT/Customer') as T2(lin)
| (No column name) |
|---|
| Order 1 |
| Order 2 |