SQL

how to get node value of xml in sql server

CREATE TABLE test(data xml);
insert into test values
('
<ROOT>
  <Customer>
    <Order>Order 1</Order>
  </Customer>
  <Customer>
    <Order>Order 2</Order>
  </Customer>
</ROOT>');
GO
-- If you need first <Order> element of your XML data you can use the next sentence.
SELECT data.value('(ROOT/Customer/Order)[1]', 'varchar(100)')
FROM   test;

-- all values
SELECT T2.lin.value('(Order)[1]', 'varchar(100)')
FROM   test
CROSS APPLY data.nodes('/ROOT/Customer') as T2(lin)