Início / dba / Perguntas / 320029
Accepted
Sir Swears-a-lot
Asked: 2022-11-22 19:09:50 +0800 CST

SQL Server XQuery Namespace

  • 772

Estou tentando consultar um xml como abaixo.

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

Aqui está a minha consulta. Preciso do Site & 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)

Estou tendo o erro a seguir:

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.

Alguém pode me dizer o que estou fazendo de errado? Qualquer ajuda ou sugestão seria muito apreciada. Eu sempre lutei com a sintaxe xquery.

sql-server-2016

1 respostas

  1. Best Answer

    Dê uma olhada em Adicionar namespaces a consultas usando WITH XMLNAMESPACES

    Sua consulta deve ser algo como isto

    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

relate perguntas