
DROP DATABASE xmldb
GO

CREATE DATABASE xmldb
GO

USE xmldb
GO

DECLARE  @x XML
SELECT @x.exist('/people/person')   -- returns NULL

SET @x = '<people><person>fred</person></people>'
SELECT @x.exist('/people/person')   -- returns 1

SET @x = '<person>sam</person>'
SELECT @x.exist('/people/person')   -- returns 0
GO

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

INSERT xml_tab VALUES('<people><person name="curly"/></people>')
INSERT xml_tab VALUES('<people><person name="larry"/></people>')
INSERT xml_tab VALUES('<people><person name="moe"/></people>')
GO

SELECT xml_col.exist('/people/person[@name="moe"]') AS is_there_moe
  FROM xml_tab
GO

-- insert one row containing an XML document with 3 persons
-- rather than 3 rows each containing an XML document with 1 person
INSERT xml_tab VALUES(
 '<people>
    <person name="curly"/>
    <person name="larry"/>
    <person name="moe"/>
  </people>')
GO

SELECT xml_col.exist('/people/person[@name="moe"]') AS is_there_moe
  FROM xml_tab
GO

SELECT the_id FROM xml_tab
  WHERE xml_col.exist('/people/person[@name="moe"]') = 1
GO
