IF OBJECT_ID('dbo.table1','U') IS NOT NULL DROP TABLE dbo.table1;IF OBJECT_ID('dbo.table2','U') IS NOT NULL DROP TABLE dbo.table2;CREATE TABLE dbo.table1 (nameId INT IDENTITY, phoneNbr VARCHAR(20), email VARCHAR(100));CREATE TABLE dbo.table2 (nameId INT IDENTITY, ContactInfo XML);INSERT dbo.table1(phoneNbr,email) VALUES ('555-444-3333','JoeBlow@gmail.com');INSERT dbo.table2(ContactInfo) VALUES ('<contactInfo> <phone>555-444-333</phone> <email>JoeBlow@gmail.com</email></contactInfo>');
-- relationalSELECT t.nameId, t.phoneNbr, t.emailFROM dbo.table1 AS tWHERE t.email IS NOT NULL-- XML:SELECT t.nameId, phoneNbr = t.ContactInfo.value('(contactInfo/phone/text())[1]', 'VARCHAR(20)'), email = t.ContactInfo.value('(contactInfo/email/text())[1]', 'VARCHAR(100)')FROM dbo.table2 AS tWHERE t.ContactInfo.value('(contactInfo/email/text())[1]', 'VARCHAR(100)') IS NOT NULL;