2

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)
WITH (
    [Order]  varchar(100)
    );
  • Consider using XML data type methods instead of xml procs. What is the criteria for returning Order 1' and not Order 2? Is it the first Order` node you want? – Dan Guzman Jul 7 '17 at 11:59
  • Check this post, it might help you dba.stackexchange.com/questions/177532/… – S4V1N Jul 7 '17 at 12:51
6
CREATE TABLE test(data xml);
insert into test values
('
<ROOT>
  <Customer>
    <Order>Order 1</Order>
  </Customer>
  <Customer>
    <Order>Order 2</Order>
  </Customer>
</ROOT>');
GO
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;
GO
| (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)
GO
| (No column name) |
| :--------------- |
| Order 1          |
| Order 2          |

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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