title: Sql query with cross join XMLTABLE
body: <p>Help needed to extract the data below from XML messages. I have table which contains the xml message in clob data type. I am trying using below query but it is not returning any data . I need to extract all the values from xml message.</p>
  
  <pre><code>&lt;iORDERS:iORDERS xmlns:iORDERS="urn:iORDERS-abcdonline-com:Integration:v1"&gt;
  &lt;ORDER_NOTIFY&gt;
  &lt;MESSAGE_DATETIME&gt;2017-06-13T12:20:51+10:00&lt;/MESSAGE_DATETIME&gt;
  &lt;MESSAGE_SEQ&gt;1&lt;/MESSAGE_SEQ&gt;
  &lt;MESSAGE_TYPE&gt;PLACED&lt;/MESSAGE_TYPE&gt;
  &lt;ORDER_HEAD&gt;
  &lt;ORDER_ID&gt;1111&lt;/ORDER_ID&gt;
  &lt;DROP_SHIP_ORDER_NO&gt;&lt;/DROP_SHIP_ORDER_NO&gt;
  &lt;CUSTOMER_ORDER_NO&gt;22222&lt;/CUSTOMER_ORDER_NO&gt;
  &lt;DISPATCH_LOCATION&gt;
  &lt;SKU&gt;2323234&lt;/SKU&gt;
  &lt;UPC&gt;4549432533626&lt;/UPC&gt;
  &lt;REQUESTED_QTY&gt;1&lt;/REQUESTED_QTY&gt;
  &lt;DISPATCH_ASSIGNMENT&gt;7777&lt;/DISPATCH_ASSIGNMENT&gt;
  &lt;PROVIDER_ID&gt;100&lt;/PROVIDER_ID&gt;
  &lt;PKG_TYPE&gt;SAT&lt;/PKG_TYPE&gt;
  &lt;/DISPATCH_LOCATION&gt;
  &lt;/ORDER_HEAD&gt;
  &lt;/ORDER_NOTIFY&gt;
  &lt;/iORDERS:iORDERS&gt;
  </code></pre>
  
  <p>query :</p>
  
  <pre><code>select wor.batch_no,wor.web_service_no,x.*
    from web_orders wo
      cross join XMLTABLE (
            XMLNAMESPACES(DEFAULT 'urn:iORDERS-abcdonline-com:Integration:v1'),
             'iORDERS/ORDER_NOTIFY/ORDER_HEAD/DISPATCH_LOCATION'
              passing xmltype(wo.xml_message)
            columns    
            MESSAGE_TYPE varchar(120) path './../../../MESSAGE_TYPE') x;
  </code></pre>