返回NULL的sql查询xml值

Question

我对xml结构没有经验，需要一个起点来了解如何从下面的xml结构中检索值。

我使用xml从webservice中获取stored-procedure，并将其存储到表中的表"StockInfoXML“字段中，其中包含xml类型的XML_Url。

<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>

我尝试过几种方法，但一直在返回null或什么都没有。

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);

有人能帮我开始吗？谢谢。

Answer 1

您的xml包含一个名称空间xmlns="http://www.webserviceX.NET/"，它是默认的命名空间。必须声明它或使用通配符作为前缀。

关于XML，有一些最佳实践：

• 尽可能具体
• 只有正向导航
• 如果的创建处于您的控制范围内，则将日期和时间格式更改为ISO8601。您的格式是特定于区域性的，很容易导致不同系统上的转换错误。最好是一个像<DateAndTime>2017-05-23T12:37:00</DateAndTime>这样的组合值

对于您的问题，有几种方法：

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>';

-最佳方法:声明默认命名空间的XMLNAMESPACES

WITH XMLNAMESPACES(DEFAULT 'http://www.webserviceX.NET/')
SELECT @xml.value(N'(/string/StockQuotes/Stock/Symbol/text())[1]',N'nvarchar(max)');

-隐式命名空间声明：

SELECT @xml.value(N'declare namespace ns="http://www.webserviceX.NET/";
                   (/ns:string/ns:StockQuotes/ns:Stock/ns:Symbol/text())[1]',N'nvarchar(max)');

-大多数情况下不推荐，但对懒惰的人有好处:-D

SELECT @xml.value(N'(//*:Symbol)[1]',N'nvarchar(max)');

--如果要读取相同级别的更多值，可以使用.nodes将当前节点设置为...<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);

Answer 2

必须在获取值之前为xml设置命名空间。

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>
      <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>'

;WITH XMLNAMESPACES('http://www.webserviceX.NET/' as ns)
SELECT 
x.s.value('(./ns:Symbol)[1]', 'varchar(50)') AS [Symbol]
FROM @xml.nodes('/ns:string/ns:StockQuotes/ns:Stock') AS x(s);

返回

Symbol
--------
ENGI.PA