Archive

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 :

<root>
  <person id="1">
    <name>John</name>
    <details>
      <phone type="mobile">111-11-2222-22</phone>
      <phone type="home">555-11-2222-22</phone>
    </details>
  </person>
  <person id="2">
    <name>Jane</name>
    <details>
      <phone type="mobile">111-11-2222-33</phone>
    </details>
  </person>
  <person id="3">
    <name>Sam</name>
    <details/>
  </person>
</root>

We’d like to get the following result set :

 PERSON_ID PERSON_NAME PHONE_TYPE PHONE_NUMBER
---------- ----------- ---------- ---------------
         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