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.
Your xml includes a namespace xmlns="http://www.webserviceX.NET/", which is the default namespace. You must either declare it or use a wildcard for the prefix.
With XML there are some best practices:
<DateAndTime>2017-05-23T12:37:00</DateAndTime>For your issue there are several approaches:
DECLARE @xml XML=
N'<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>
<!--more elements -->
</Stock>
</StockQuotes>
</string>';
--Best approach: XMLNAMESPACES to declare the default namespace
WITH XMLNAMESPACES(DEFAULT 'http://www.webserviceX.NET/')
SELECT @xml.value(N'(/string/StockQuotes/Stock/Symbol/text())[1]',N'nvarchar(max)');
--Implicit namespace declaration:
SELECT @xml.value(N'declare namespace ns="http://www.webserviceX.NET/";
(/ns:string/ns:StockQuotes/ns:Stock/ns:Symbol/text())[1]',N'nvarchar(max)');
--Not recommended in most cases, but good for lazy people :-D
SELECT @xml.value(N'(//*:Symbol)[1]',N'nvarchar(max)');
--If you want to read more values of the same level, you can use .nodes to set the current node to ...<Stock>.
WITH XMLNAMESPACES(DEFAULT 'http://www.webserviceX.NET/')
SELECT st.value('(Symbol/text())[1]',N'nvarchar(max)')
,st.value('(Last/text())[1]',N'decimal(10,4)')
--more nodes
FROM @xml.nodes(N'/string/StockQuotes/Stock') AS A(st);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments