Select multiple XML nodes out of each row in a SQL table

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)

This entry was posted in CodeMinder, SQL, XML. Bookmark the permalink.