主页 / dba / 问题 / 320029
Accepted
Sir Swears-a-lot
Asked: 2022-11-22 19:09:50 +0800 CST

SQL Server XQuery 命名空间

  • 772

我正在尝试查询如下所示的 xml。

<wfs:FeatureCollection xmlns:gml="http://www.opengis.net/gml" xmlns:wfs="http://www.opengis.net/wfs" xmlns:ogc="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <gml:featureMember>
    <SiteList>
      <Site>123 ABC Road</Site>
      <Location>
        <gml:Point>
          <gml:pos>-38.987654321 177.12345678</gml:pos>
        </gml:Point>
      </Location>
    </SiteList>
  </gml:featureMember>
</wfs:FeatureCollection>

这是我的查询。我需要网站 & gml:pos (lat/Long)

select 
    xml.xmldata.value('(wfs:FeatureCollection/gml:featureMember/SiteList/Site)[1]', 'varchar(250)') as [SiteName],  
    xml.xmldata.value('(wfs:FeatureCollection/gml:featureMember/SiteList/Location/gml:Point/gml:pos)[1]', 'varchar(250)') as [Location]
    from  #xml t
    cross apply t.yourXML.nodes('//wfs:FeatureCollection/gml:featureMember/SiteList') xml (xmldata)

我收到以下错误：

Msg 2229, Level 16, State 1, Procedure dbo.DW_sp_Extract_Sites, Line 102 [Batch Start Line 2]
XQuery [#xml.yourXML.nodes()]: The name "wfs" does not denote a namespace.

谁能告诉我我做错了什么？任何帮助或建议将不胜感激。我一直在与 xquery 语法作斗争。

sql-server-2016

1 个回答

  1. Best Answer

    查看使用 WITH XMLNAMESPACES 将名称空间添加到查询

    您的查询应该是这个样子

    with xmlnamespaces('http://www.opengis.net/gml' as gml,
                   'http://www.opengis.net/wfs' as wfs) 
select 
    xml.xmldata.value('(Site/text())[1]', 'varchar(250)') as [SiteName],  
    xml.xmldata.value('(Location/gml:Point/gml:pos/text())[1]', 'varchar(250)') as [Location]
    from  #xml t
    cross apply t.yourXML.nodes('//wfs:FeatureCollection/gml:featureMember/SiteList') xml (xmldata);
    • 2

相关问题