Archive for May, 2016

How To : using OUTER JOIN with XMLTABLE or XQuery

May 20, 2016 8 comments

Here is a short reminder post about how to use or implement outer join in XMLTABLE or XQuery.

The typical scenario for which we would need to outer join is the flattening of a master-detail hierarchical structure with some logical records missing in the detail part.
For instance, given this input XML document :

  <person id="1">
      <phone type="mobile">111-11-2222-22</phone>
      <phone type="home">555-11-2222-22</phone>
  <person id="2">
      <phone type="mobile">111-11-2222-33</phone>
  <person id="3">

We’d like to get the following result set :

---------- ----------- ---------- ---------------
         1 John        mobile     111-11-2222-22
         1 John        home       555-11-2222-22
         2 Jane        mobile     111-11-2222-33
         3 Sam                    

Read more…

Categories: HowTo, XML DB, XQuery