XML
XML
CodeMinder: to remind myself of stuff that I usually have to look up each time I do it. To delete all PersonAlternativeName nodes from XmlColumn of the People table: with xmlnamespaces(
'http://mydomain.com/person/extension/0.1' as px)
update People
set XmlColumn.modify('delete //px:PersonAlternateName')
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)