patternsqlMinor

Read inside node value of Xml using Sql Server

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

readsqlnodexmlvalueusingserverinside

Problem

I want to read node values of xml in sql, my xml look like this

I just want to read value of order node that is 'Order 1'
and my code is

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT * FROM OPENXML (@idoc, '/ROOT/Customer/Order',0)
WITH (
    [Order]  varchar(100)
    );

Solution

CREATE TABLE test(data xml);
insert into test values
('

  
    Order 1
  
  
    Order 2
  
');
GO

1 rows affected

If you need first `

 element of your XML data you can use the next sentence.

SELECT data.value('(ROOT/Customer/Order)[1]', 'varchar(100)')
FROM   test;
GO


| (No column name) |
| :--------------- |
| Order 1          |

If you need to select all

` elements you can use a CROSS APPLY solution with .nodes()

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

Code Snippets

CREATE TABLE test(data xml);
insert into test values
('
<ROOT>
  <Customer>
    <Order>Order 1</Order>
  </Customer>
  <Customer>
    <Order>Order 2</Order>
  </Customer>
</ROOT>');
GO

SELECT data.value('(ROOT/Customer/Order)[1]', 'varchar(100)')
FROM   test;
GO

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

Context

StackExchange Database Administrators Q#178247, answer score: 7

Revisions (0)

No revisions yet.