Querying XML data in a set operation is relatively straight forward. Here's an excerpt from a query I have to retrieve content from a table with XML content, in this case subscriber information from a healthcare claim. First two columns in the query result are physical columns in the table, the rest come from the XML content.
Note that there's also a call to a custom assembly (NLXDecrypt) to decrypt values stored in the XML payload - portions of the content is stored encrypted to hide from prying eyes ;-).
;WITH XMLNAMESPACES ('http://Novologix.Shared.Payload.Library.Domain.Schemas' AS cm)
SELECT TOP 100 ClaimTransactionId, AddDate,
claim.content.value('SubmitterClaimIdentifier[1]','varchar(max)') SubmitterClaim,
claim.content.value('cm:NLXEntityDiagnosis[1]/DiagnosisCode[1]','varchar(max)') PrimaryDiagnosis,
claim.content.value('count(cm:NLXEntityServiceLine)','int') Lines,
admindb.dbo.NLXDecrypt(claim.content.value('cm:NLXEntityMember[1]/MemberId[1]','varchar(max)')) MemberId,
claim.content.value('cm:NLXEntityMember[1]/ClaimRole[1]','varchar(max)') ClaimRole,
admindb.dbo.NLXDecrypt(claim.content.value('cm:NLXEntityMember[1]/DateOfBirth[1]','varchar(max)')) DOB,
admindb.dbo.NLXDecrypt(claim.content.value('cm:NLXEntityMember[1]/cm:NLXEntityPerson[1]/FirstName[1]','varchar(max)')) FirstName,
admindb.dbo.NLXDecrypt(claim.content.value('cm:NLXEntityMember[1]/cm:NLXEntityPerson[1]/LastName[1]','varchar(max)')) LastName,
claim.content.value('cm:NLXEntityMember[1]/cm:NLXEntityPerson[1]/Gender[1]','varchar(max)') Gender
FROM dbo.ClaimTransaction
CROSS APPLY NLXClaim.nodes('/cm:NLXTransactionClaim/cm:NLXEntityClaim') claim(content)
ORDER BY ClaimTransactionId DESC