CodeMinder: to remind myself of stuff that I usually have to look up each time I do it.
If a People table has a column named XML, and the XML column may specify multiple PersonAlternativeName entities, then to get all PersonAlternativeNames in a single result set use CROSS APPLY or OUTER APPLY.
with xmlnamespaces(
'http://mydomain.com/person/extension/0.1' as px,
'http://niem.gov/niem/niem-core/2.0' as n)
select p.PersonId,
n.value('(n:PersonGivenName)[1]', 'varchar(100)') as FN,
n.value('(n:PersonMiddleName)[1]', 'varchar(100)') as MN,
n.value('(n:PersonSurName)[1]', 'varchar(100)') as LN
from People p
cross apply XmlColumn.nodes('//px:PersonAlternateName') as ppl(n)
Comments !