How we can read all OrderId. I am using the following code to read but it is reading only first orderId Sample Code DECLARE @InputXMLHandle int Declare @RequestXml xml = ' RSIN10095 RSIN10096 ' EXEC sp_xml_preparedocument @InputXMLHandle OUTPUT, @RequestXml Select * From OPENXML(@InputXMLHandle,'/Request/OrderStatus') WITH ( OrderId varchar(50) 'OrderId' )

I got the solutions for this problem. by replacing '/Request/OrderStatus' to '/Request/OrderStatus/OrderId' and OrderId varchar ( 50 ) 'OrderId' to OrderId varchar ( 50 ) '.'

Answer

How to read repeated node value in SQL Server

Praveen Kumar

660

How we can read all OrderId. I am using the following code to read but it is reading only first orderId

Sample Code

DECLARE @InputXMLHandle int
Declare @RequestXml xml = '
<Request>
  <OrderStatus>
    <OrderId>RSIN10095</OrderId>
    <OrderId>RSIN10096</OrderId>
  </OrderStatus>
</Request>
'
EXEC sp_xml_preparedocument @InputXMLHandle OUTPUT, @RequestXml
    
Select *
From OPENXML(@InputXMLHandle,'/Request/OrderStatus')
WITH
(     
    OrderId varchar(50) 'OrderId'
)