If we have xml file like
<Countries>
<Country>India</Country>
<Country>USA</Country>
<Country>UK</Country>
<Country>China</Country>
<Country>Shrilanka</Country>
</Countries>
If we want to get all countries, we can write query like
following way..
SELECT
CAST(xmlFile.Country.query('data(.)') AS varchar(50)) AS [Country]
FROM @xml.nodes('/Countries/Country') AS xmlFile(Country)
Where @xml variable declare as XML and assign the xmlfile.
Full Example is as below.
DECLARE @xml XML
SET @xml='<Countries>
<Country>India</Country>
<Country>USA</Country>
<Country>UK</Country>
<Country>China</Country>
<Country>Shrilanka</Country>
</Countries>'
SELECT
CAST(xmlFile.Country.query('data(.)') AS varchar(50)) AS [Country]
FROM @xml.nodes('/Countries/Country') AS xmlFile(Country)