sql query xml values returning NULL

Viewed 6437

I'm not experienced with the xml structure and need a start-point to how I can retrieve the values from xml structure below.

I fetch the xml from a webservice using a stored-procedure and store to a table "StockInfoXML" Field in table holding the xml is XML_Url of type xml.

<string xmlns="http://www.webserviceX.NET/">
  <StockQuotes>
    <Stock>
      <Symbol>ENGI.PA</Symbol>
      <Last>13.53</Last>
      <Date>5/23/2017</Date>
      <Time>12:37pm</Time>
      <Change>+0.06</Change>
      <Open>13.45</Open>
      <High>13.59</High>
      <Low>13.40</Low>
      <Volume>1524437</Volume>
      <MktCap>32.95B</MktCap>
      <PreviousClose>13.47</PreviousClose>
      <PercentageChange>+0.48%</PercentageChange>
      <AnnRange>10.77 - 15.20</AnnRange>
      <Earns>-0.23</Earns>
      <P-E>N/A</P-E>
      <Name>ENGIE</Name>
    </Stock>
  </StockQuotes>
</string>

I've tried a couple of things but keep returning null or nothing.

declare @X XML;

SELECT
@X = XML_Url
FROM dbo.StockExchangeInfoXML

SELECT 
x.s.value('(StockQuotes/Stock/Symbol)[1]', 'nvarchar(50)') AS [Symbol]
FROM @X.nodes('./StockQuotes/Stock') AS x(s);

Anyone who can get me started? Thanks.

2 Answers
Related