How to read XML repeated nodes in SQL Server

Question

I am using the following code to read OrderId but it is showing only first

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'
)

Answer 1

Use full path

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/OrderId')
WITH
(     
    OrderId varchar(50) '.'
)

or using xml methods

Declare @RequestXml xml = '<Request><OrderStatus>
                                       <OrderId>RSIN10095</OrderId>
                                       <OrderId>RSIN10096</OrderId>
                                    </OrderStatus>
                            </Request>';
select t.n.value('.[1]', 'varchar(50)') id
from @RequestXml.nodes('/Request/OrderStatus/OrderId') t(n);