USE xmldb
GO

DECLARE @x XML
SET @x = '
<customers>
  <customer>
    <givenName>Jill</givenName><givenName>Rodgers</givenName>
    <country>US</country>
  </customer>
  <customer>
    <givenName>Juan</givenName><givenName>Rodriguez</givenName>
    <country>Costa Rica</country>
  </customer>
</customers>'

SELECT 
  tab.col.value('givenName[1]', 'VARCHAR(20)') AS FirstName,
  tab.col.value('givenName[1]', 'VARCHAR(20)') AS GivenName,
  tab.col.value('country[1]', 'VARCHAR(20)') AS Country,
  tab.col.exist('country/text()[.="US"]') AS Local
FROM 
@x.nodes('//customer') tab(col)
GO

DROP TABLE xml_tab
GO

CREATE TABLE xml_tab(
  the_id INTEGER PRIMARY KEY IDENTITY,
  xml_col XML)
GO

INSERT xml_tab VALUES('<people><person><name>curly</name></person></people>')
INSERT xml_tab VALUES('<people><person><name>larry</name></person></people>')
INSERT xml_tab VALUES('<people><person><name>moe</name></person></people>')
INSERT xml_tab values('
  <people>
     <person><name>laurel</name></person>
     <person><name>hardy</name></person>
  </people>')
GO

SELECT the_id, tab.col.value('text()[1]', 'VARCHAR(20)') AS name 
  FROM xml_tab 
  CROSS APPLY 
    xml_col.nodes('/people/person/name') AS tab(col)
GO

-- add a "nameless person"
INSERT xml_tab VALUES('<people><person></person></people>')
GO

SELECT the_id, 
       tab.col.value('l_name[1]/text()[1]', 'varchar(20)') AS l_name 
  FROM xml_tab 
  CROSS APPLY 
    xml_col.nodes('/people/person/name') AS tab(col)
GO

SELECT the_id, 
       tab.col.value('l_name[1]/text()[1]', 'varchar(20)') AS l_name 
  FROM xml_tab 
  OUTER APPLY 
    xml_col.nodes('/people/person/name') AS tab(col)
GO