SQL Server XQuery Namepace

Question

I'm trying to query an xml like below.

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

Here is my query. I need the 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)

I get the following error:

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.

Can anyone tell me what I'm doing wrong? Any help or suggestions would be greatly appreciated. I've always struggled with xquery syntax.

Answer 1

Have a look at Add namespaces to queries using WITH XMLNAMESPACES

Your query should look something like this

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