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

enter image description here

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)
    [Order]  varchar(100)

1 Answer 1

CREATE TABLE test(data xml);
insert into test values
    <Order>Order 1</Order>
    <Order>Order 2</Order>
1 rows affected

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;
| (No column name) |
| :--------------- |
| Order 1          |

If you need to select all <Order> 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)
| (No column name) |
| :--------------- |
| Order 1          |
| Order 2          |

dbfiddle here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.