SQL Server 2016

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