1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

DECLARE @XmlVar1 XML
SET @XmlVar1=
       '<CustomerInfo>
        <Customers>
              <CustomerID>A10001</CustomerID>
              <CountryName>IRAN</CountryName>
       </Customers>
        <Customers>
              <CustomerID>A10002</CustomerID>
              <CountryName>IRAQ</CountryName>
       </Customers>
        </CustomerInfo>'
select
    t.x.value('CustomerID[1]','nvarchar(100)') as CustomerID,
    t.x.value('CountryName[1]','nvarchar(100)') as CountryName
from @XmlVar1.nodes('/CustomerInfo/Customers') t(x)