
CREATE FUNCTION checkperson(@data xml)
RETURNS BIT AS
BEGIN
RETURN @data.exist('/people/person')
END 
 
CREATE TABLE xmltab(
  id INTEGER PRIMARY KEY,
  pdoc XML 
   CHECK (dbo.checkperson(pdoc)=1)
-- ok
)

delete xmltab

INSERT xmltab  
VALUES(1,'<people><person name="curly"/></people>')
INSERT xmltab 
VALUES(2,'<people><person name="larry"/></people>')
INSERT xmltab 
  VALUES(3,'<people><person name="moe"/></people>')


declare @x xml
set @x = (select * from xmltab for xml auto, elements)
select @x.query('xmltab/pdoc/people/person')

select pdoc.value('/people[1]/person[2]/@name', 'varchar(20)')
 from xmltab




insert xmltab values(3, '<people><t:person xmlns:t="http://some" name="bob"/></people>')
insert xmltab values(2, '<people><t:person name="bob"/><person name="bob"/><person name="bob"/></people>')


CREATE FUNCTION getidvaluex(@invoicex xml)
RETURNS INT AS
BEGIN
RETURN invoicex.value('*[1]/@ID','INT')
END 

drop table Invoices
CREATE TABLE Invoices (
  id INT PRIMARY KEY,
  invoice xml,
CONSTRAINT id_chk CHECK(
  dbo.getidvaluex(invoice)=id)
)

insert invoices values(1, '<Invoice ID="2"/>')

declare @xmldoc xml
set @xmldoc = ''
SELECT @xmldoc.query(' 
for $p in (1,2,3)
return <number>{$p}</number>') 