Wednesday 3 April 2013

How to get same node values from the xml file using XQuery?


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)

No comments:

Post a Comment