Re: 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();

You don't have to "try" but you have to learn the basis of XPath syntax. The expression you wrote has 13 matches. The following expression returns exactly what you want:

xml.EXTRACT('//DescriptiveMetadata/Property/Property[_at_FormalName=''StockSymbol'']/_at_Value').getstringval(); Received on Fri Aug 09 2013 - 08:59:34 CEST