Saturday, September 26, 2009

SQL Read XML

DECLARE @x XML
SET @x = '<?xml version="1.0" encoding="utf-8" ?>
<Countries>
<Country>
<Name>United States</Name>
<ISOCode>US</ISOCode>
<Language>English</Language>
</Country>
<Country>
<Name>Costa Rica</Name>
<ISOCode>CR</ISOCode>
<Language>Spanish</Language>
</Country>
<Country>
<Name>Belize</Name>
<ISOCode>BZ</ISOCode>
<Language>English</Language>
</Country>
</Countries>'

SELECT T.C.value('Name[1]', 'VARCHAR(255)'),
T.C.value('ISOCode[1]', 'VARCHAR(2)'),
T.C.value('Language[1]', 'VARCHAR(255)')
FROM @x.nodes('Countries/Country') AS T(C)