create xmlschema '<?xml-version = "1.0" encoding="utf-8" ?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.mytest.com/schemas/phone" xmlns= "http://www.mytest.com/schemas/phone" elementFormDefault="qualified"> <xsd:element name="PhoneNumbers"> <xsd:complexType> <xsd:sequence> <xsd:element maxOccurs="unbounded" name="Phone"> <xsd:complexType> <xsd:attribute name="Type" type="xsd:string" use="required" /> <xsd:attribute name="Number" type="xsd:string" use="required" /> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>'Then created a table:
create TABLE Customers ( CustomerID uniqueidentifier primary key DEFAULT newid(), Name char(50), PhoneNumbers xml ('http://www.mytest.com/schemas/phone'))Then inserted a few records:
insert into customers (Name, PhoneNumbers) values ('Jurden, Dan', '<PhoneNumbers xmlns= "http://www.mytest.com/schemas/phone"> <Phone Type="Cell" Number="9792514509"/> </PhoneNumbers>') insert into customers (Name, PhoneNumbers) values ('Jurden, Ann', '<PhoneNumbers xmlns= "http://www.mytest.com/schemas/phone"> <Phone Type="Home" Number="9798305351"/> </PhoneNumbers>')So far, so good. Now I try to use some of the XML methods and cannot get them to work. For example, this should return the Customer "Jurden, Dan". But it returns nothing.
select customerid, name, phonenumbers from customers where phonenumbers::exist('namespace PN="http://www.mytest.com/schemas/phone" /PN:PhoneNumbers/Phone[@Type="Cell"]')=1What am I doing wrong?