• 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