2

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.

1
  • Side note // descendant axis is slow, and in this case unnecessary. Just use the normal / child axis Nov 22, 2022 at 13:21

1 Answer 1

2

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);
2
  • Hmm.. I tried insert that into my stored proc and now it's complaining about syntax around 'with xmlnamespaces...' So i need to look further, but thank you for your help. Nov 28, 2022 at 23:51
  • @SirSwears-a-lot You need to use ; at the end of previous statement in the procedure. Nov 30, 2022 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.