CREATE DATABASE xmldb
GO

USE xmldb
GO


CREATE TABLE xml_tab (
the_id INTEGER,
xml_col XML
)
GO

-- XML column cannot be primary key
CREATE TABLE xml_tab (
xml_col XML PRIMARY KEY
)
GO

-- pdoc must have a person element
-- as a child of the people root
-- create the wrapper UDF
CREATE FUNCTION check_for_person (@thedoc XML)
RETURNS BIT
AS
BEGIN
-- function that uses XQuery, see next chapter
RETURN @thedoc.exist('/people/person')
END
GO

CREATE TABLE xmltab(
  id INTEGER PRIMARY KEY,
  pdoc XML CHECK(dbo.check_for_person(pdoc)=1)
)
