XMLTABLE

I have some XML stored in a PLSQL variable of XMLType:

      <DescriptiveMetadata>
        <Language FormalName="en"/>
        <Property FormalName="PublicCompany" Vocabulary="urn:newsml:comtexnews.net:20010201:DomesticPublicCompanies:1">


          <Property FormalName="CompanyName" Value="Stanley Black &amp; Decker"/>

          <Property FormalName="StockSymbol" Value="SWK"/>

          <Property FormalName="StockExchange" Value="New York"/>

          <Property FormalName="SICCode" Value="3545"/>

          <Property FormalName="CUSIP" Value="854101"/>

          <Property FormalName="SEDOL" Value="B3Q2FJ4"/>

          <Property FormalName="ISINNumber" Value="US85021011"/>

          <Property FormalName="MarketStatus" Value="ACTV"/>

          <Property FormalName="SecurityType" Value="Common Stock"/>

          <Property FormalName="Country" Value="UNITED STATES"/>

          <Property FormalName="IndustryGroup" Value="Hand/Machine Tools"/>

          <Property FormalName="IndustrySubGroup" Value="Tools-Hand Held"/>

          <Property FormalName="IndustrySector" Value="Industrial"/>

        </Property>
      </DescriptiveMetadata>

I am looking to get the StockSymbol value: SWK. I tried this but it gets me everything:

EXTRACT('/NewsML/NewsItem/NewsComponent/DescriptiveMetadata/Property/Property/_at_Value').getstringval();

I only want to get it when the FormalName = 'StockSymbol'. I'm doing this in PL/SQL. I'm having trouble getting any code to work on extracting the value I want.

Can anyone help?

Thanks! Received on Thu Aug 08 2013 - 16:17:22 CEST