If you only occasionally dabble in XML and XPath utilizing the XML data type in SQL Server can be a bit daunting. I found it very useful in passing parameters to stored procedures containing object graphs. The same can be accomplished the table parameter if you only have a list of one type of objects, but if you need to pass an object graph of parent/child objects of different types, using XML will be your best bet.
Below I demonstrate how one could represent a list of Contact objects in an XML document and subsequently access the data via a select statement. the @Contacts parameter could be used as a parameter to a stored procedure if you’re running SQL 2005 or higher. Use your imagination for the rest.
declare @Contacts xml
set @Contacts =
‘<root>
<Contact>
<ContactID>1</ContactID>
<Email>contact1@contact1.com</Email>
</Contact>
<Contact>
<ContactID>2</ContactID>
<Email>contact2@contact2.com</Email>
</Contact>
<Contact>
<ContactID>3</ContactID>
<Email>contact3@contact3.com</Email>
</Contact>
</root>’
select
ContactID = Contact.value(‘ContactID[1]‘, ‘int’),
Email = Contact.value(‘Email[1]‘, ‘varchar(255)’)
from
@Contacts.nodes(‘root/Contact’) as Contacts([Contact])
