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.

<span style="color: #0000FF;">with</span><span style="color: #000000;"> xmlnamespaces(
    </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">http://mydomain.com/person/extension/0.1</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> px,
    </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">http://niem.gov/niem/niem-core/2.0</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> n)
</span><span style="color: #0000FF;">select</span><span style="color: #000000;"> p.PersonId,
    n.value(</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">(n:PersonGivenName)[1]</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">varchar(100)</span><span style="color: #FF0000;">'</span><span style="color: #000000;">) </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> FN,
    n.value(</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">(n:PersonMiddleName)[1]</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">varchar(100)</span><span style="color: #FF0000;">'</span><span style="color: #000000;">) </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> MN,
    n.value(</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">(n:PersonSurName)[1]</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">varchar(100)</span><span style="color: #FF0000;">'</span><span style="color: #000000;">) </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> LN
</span><span style="color: #0000FF;">from</span><span style="color: #000000;"> People p
</span><span style="color: #0000FF;">cross</span><span style="color: #000000;"> apply XmlColumn.nodes(</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">//px:PersonAlternateName</span><span style="color: #FF0000;">'</span><span style="color: #000000;">) </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> ppl(n)</span>

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