title: CROSS APPLY SQL Server query on Hive
body: <p>HDP-2.5.0.0 using Ambari 2.4.0.1</p>
  
  <p>The Hive table ReportSetting is as follows :</p>
  
  <pre><code>id int
  
  serializedreportsetting String
  </code></pre>
  
  <p>The column 'serializedreportsetting' is an XML data type in the source SQL Server db but is converted to String during Sqoop import, this is how it looks in SQL Server :</p>
  
  <pre><code>&lt;ReportSettings4 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;Trigger&gt;
    &lt;Manual&gt;true&lt;/Manual&gt;
    &lt;/Trigger&gt;
  &lt;StartTime&gt;
      &lt;Year&gt;8&lt;/Year&gt;
      &lt;Month&gt;1&lt;/Month&gt;
      &lt;Day&gt;1&lt;/Day&gt;
      &lt;Hour&gt;0&lt;/Hour&gt;
      &lt;Minute&gt;0&lt;/Minute&gt;
    &lt;/StartTime&gt;
    &lt;ReportPeriod&gt;
      &lt;Month&gt;0&lt;/Month&gt;
      &lt;Day&gt;0&lt;/Day&gt;
      &lt;Hour&gt;0&lt;/Hour&gt;
      &lt;Minute&gt;5&lt;/Minute&gt;
    &lt;/ReportPeriod&gt;
    &lt;Theft&gt;
      &lt;DigitalInput&gt;true&lt;/DigitalInput&gt;
      &lt;Can&gt;false&lt;/Can&gt;
    &lt;/Theft&gt;
    &lt;SequenceNo&gt;0&lt;/SequenceNo&gt;
  &lt;/ReportSettings4&gt;
  </code></pre>
  
  <p>In Hive table :</p>
  
  <pre><code>&lt;ReportSettings4 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;Trigger&gt;&lt;Manual&gt;true&lt;/Manual&gt;&lt;/Trigger&gt;&lt;StartTime&gt;&lt;Year&gt;8&lt;/Year&gt;&lt;Month&gt;12&lt;/Month&gt;&lt;Day&gt;31&lt;/Day&gt;&lt;Hour&gt;23&lt;/Hour&gt;&lt;Minute&gt;34&lt;/Minute&gt;&lt;/StartTime&gt;&lt;ReportPeriod&gt;&lt;Month&gt;0&lt;/Month&gt;&lt;Day&gt;0&lt;/Day&gt;&lt;Hour&gt;4&lt;/Hour&gt;&lt;Minute&gt;0&lt;/Minute&gt;&lt;/ReportPeriod&gt;&lt;Theft&gt;&lt;DigitalInput&gt;false&lt;/DigitalInput&gt;&lt;Can&gt;false&lt;/Can&gt;&lt;/Theft&gt;&lt;SequenceNo&gt;3&lt;/SequenceNo&gt;&lt;/ReportSettings4&gt;
  </code></pre>
  
  <p>The query that works fine on the SQL Server :</p>
  
  <pre><code>SELECT
  r.VehicleId
  ,rs.value('(Trigger/Manual)[1]', 'bit') AS RS_Trigger_Manual, ,CAST(CONCAT(CASE WHEN rs.value('(StartTime/Year)[1]', 'int') &lt; 10 THEN CONCAT('200',rs.value('(StartTime/Year)[1]', 'int')) ELSE CONCAT('20',rs.value('(StartTime/Year)[1]', 'int')) END,'-',rs.value('(StartTime/Month)[1]', 'int'),'-',rs.value('(StartTime/Day)[1]', 'int'),' ',rs.value('(StartTime/Hour)[1]', 'int'),':',rs.value('(StartTime/Minute)[1]', 'int'),':','00.000') AS datetime) AS RS_StartTime
  ,rs.value('(ReportPeriod/Month)[1]', 'int') AS RS_ReportPeriod_Month
  ,rs.value('(ReportPeriod/Day)[1]', 'int') AS RS_ReportPeriod_Day
  ,rs.value('(ReportPeriod/Hour)[1]', 'int') AS RS_ReportPeriod_Hour
  ,rs.value('(ReportPeriod/Minute)[1]', 'int') AS RS_ReportPeriod_Minute
  ,rs.value('(Theft/DigitalInput)[1]', 'bit') AS RS_Theft_DigitalInput
  ,rs.value('(Theft/Can)[1]', 'bit') AS RS_Theft_Can,rs.value('(SequenceNo)[1]', 'int') 
  
  AS RS_SequenceNo FROM ReportSetting r
    CROSS APPLY SerializedReportSetting.nodes('/*') AS ReportSettings(rs)
  </code></pre>
  
  <p>I could think of/do the following :</p>
  
  <ol>
  <li>To use the CROSS APPLY, I guess <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView#LanguageManualLateralView-Example" rel="nofollow noreferrer">lateral view</a> needs to be used, here I don't have the serializedreportsetting as an array, so explode() won't work. Can someone validate if I'm thinking in the right direction</li>
  <li><p>I simply tried to get the data in serializedreportsetting as columns using the <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+XPathUDF" rel="nofollow noreferrer">built-in xpath udf</a>, however, I don't get any records, few trials are as follows :</p>
  
  <p>select xpath(SerializedReportSetting,'/*') from ReportSetting limit 1;</p>
  
  <p>select xpath(SerializedReportSetting,'/ReportSettings4') from ReportSetting limit 1;</p>
  
  <p>select xpath(SerializedReportSetting,'/Trigger/Manual') from ReportSetting limit 1;</p></li>
  </ol>
  
  <p>**********UPDATE-1**********</p>
  
  <p>I used the regexp_replace to handle the above challenge :</p>
  
  <pre><code>SELECT id,
    xpath_string(SerializedReportSetting,'/ReportSettings/Trigger/Manual')        AS RS_Trigger_Manual,
    xpath_string(SerializedReportSetting,'/ReportSettings/Trigger/DriveChange')   AS RS_Trigger_DriveChange
  FROM
    (SELECT id,
      regexp_replace(SerializedReportSetting, 'ReportSettings+\\d','ReportSettings') AS SerializedReportSetting
    FROM reportsetting
    WHERE id IN (1701548,3185,1700231,1700232)
    ) reportsetting_regex;
  </code></pre>