How to get the XML node data in SQL server with out using full path of node like extractvalue function in oracle?

See more:

XML

Oracle

SQL-Server-2008R2

Hi,

Currently I am migrating Oracle code to SQL server code.During this activity I have observed XML behavior differently when compared to SQL server.

To get any node value in a XML ,SQL server requires fully qualified path.

Select  c.value('@COUNT[1]', 'nvarchar(10)') 
  from Table_1 as EC  
  
  CROSS APPLY EC.COMPUTED_XML.nodes('/PREPROCESSED_SAMPLE_DATA/OPERATION/BIT_FAULTS/FAULT') as t(c)

But in oracle I observed ExtractValue function, where it accepts the node itself and get the corresponding value.There is no necessary to give full path

  Select  x.*
from Table_1 ec ,TABLE(xmlsequence(EXTRACT(ec.computed_xml, '//FAULT'))) x

I understood from requirement that XML format should not be same for all the time.Some time particular node will be present in first level some time on other level.As oracle has flexibility to retrieve the data any where in the XML.

Now am trying to do the same functionality in Sql server but I realized with out qualified path I couldn't able to proceed further.

Could you please help me to replicate oracle extract function in sql server like simply specifying node to get its value at any level?

Thanks in advance

sample XML here 
<PREPROCESSED_SAMPLE_DATA >
  <OPERATION STATE="Flight" STATE_INDEX="7" CHRON_INDEX="8">
    <FAULT_GROUPS COUNT="0" />
    <BIT_FAULTS COUNT="3">
      <FAULT COUNT="6">
        <FAULT_CODE>210</FAULT_CODE>
      </FAULT>
      <FAULT COUNT="1">
        <FAULT_CODE>211</FAULT_CODE>
      </FAULT>
      <FAULT COUNT="6">
        <FAULT_CODE>237</FAULT_CODE>
      </FAULT>
    </BIT_FAULTS>
    <DETECTED_FAULTS COUNT="0" />
    <EVENTS COUNT="2">
      <EVENT INDEX="1" EVENT_RECORD_NUM="2">
        <TRIGGER_ID>1</TRIGGER_ID>
        <TIMESTAMP>1911:08:39.8</TIMESTAMP>
      </EVENT>
      <EVENT INDEX="2" EVENT_RECORD_NUM="1">
        <TRIGGER_ID>1</TRIGGER_ID>
        <TIMESTAMP>1911:07:39.9</TIMESTAMP>
      </EVENT>
    </EVENTS>
    <EXCEEDANCES COUNT="0" />
  </OPERATION>
  <OPERATION STATE="Flight" STATE_INDEX="6" CHRON_INDEX="7">
    <EVENTS COUNT="0" />
    <EXCEEDANCES COUNT="0" />
  </OPERATION>
  <OPERATION STATE="Flight" STATE_INDEX="5" CHRON_INDEX="6">
    <EVENTS COUNT="0" />
    <EXCEEDANCES COUNT="0" />
  </OPERATION>
  <OPERATION STATE="Flight" STATE_INDEX="4" CHRON_INDEX="5">
    <EVENTS COUNT="0" />
    <EXCEEDANCES COUNT="0" />
  </OPERATION>
  <OPERATION STATE="Ground Run" STATE_INDEX="1" CHRON_INDEX="4">
    <FAULT_GROUPS COUNT="1">
      <FAULT_GROUP INDEX="1">
        <GROUP_CODE>PT2AI</GROUP_CODE>
        <GROUP_TSTAMP>1907:31:56.6</GROUP_TSTAMP>
        <GROUP_SEVERITY>4</GROUP_SEVERITY>
        <BELIEF>0.5</BELIEF>
        <FAULTS COUNT="2">
          <FAULT COUNT="2">
            <FAULT_CODE>37</FAULT_CODE>
          </FAULT>
          <FAULT COUNT="2">
            <FAULT_CODE>34</FAULT_CODE>
          </FAULT>
        </FAULTS>
      </FAULT_GROUP>
    </FAULT_GROUPS>
    <BIT_FAULTS COUNT="0" />
    <DETECTED_FAULTS COUNT="0" />
    <EVENTS COUNT="0" />
    <EXCEEDANCES COUNT="0" />
  </OPERATION>
  <OPERATION STATE="Flight" STATE_INDEX="3" CHRON_INDEX="3">
    <FAULT_GROUPS COUNT="0" />
    <BIT_FAULTS COUNT="0" />
    <DETECTED_FAULTS COUNT="0" />
    <EVENTS COUNT="0" />
    <EXCEEDANCES COUNT="0" />
  </OPERATION>
  <FAULT_HISTORY>
    <FAULT>
      <OPERATION_STATE_INDEX>7</OPERATION_STATE_INDEX>
      <CHRONO_INDEX>8</CHRONO_INDEX>
      <FAULT_CODE>237</FAULT_CODE>
      <FAULT_TSTAMP>1911:08:29.9</FAULT_TSTAMP>
      <FAULT_SEVERITY>2</FAULT_SEVERITY>
    </FAULT>
  </FAULT_HISTORY>
</PREPROCESSED_SAMPLE_DATA>

What I have tried:

When I tried in SQL

Select c.value('@COUNT[1]', 'nvarchar(10)')
from Table_1 as EC
CROSS APPLY EC.COMPUTED_XML.nodes('/PREPROCESSED_SAMPLE_DATA/OPERATION/BIT_FAULTS/FAULT') as t(c)

I got
6
1
6
the same XML in oracle
SELECT to_number(extractVALUE (value (node),'/FAULT/@COUNT' ) )
FROM Table_1 ec ,
TABLE (xmlsequence (extract( ec.computed_xml, '//OPERATION//FAULT' ) ) ) node

I got the result set
6
1
6
2
2