XML query returns 0 rows

Transact-SQL
1

Hello there. I am trying to query an XML file.

my code below is what im using.

select top 100

x.Envelope.value('(srsName)[1]', 'varchar(255)') as 'srsName'

from (
select convert(xml, x) as BulkCOlumns
from openRowset (
bulk 'C:\Completed_file\xml_File.xml',
single_blob) as T(x)
) as T(x)

cross apply x.nodes('/Envelope[srsName]') as x(Envelope)

<gml:Envelope srsName="urn:ogc:def:crs:EPSG::27700">
<gml:lowerCorner>0 0</gml:lowerCorner>
<gml:upperCorner>1225000 660000</gml:upperCorner>
</gml:Envelope>

...........

after it runs for 3 mins I get 0 rows affected.

Just wondering, am I referencing the node incorrectly.???

Regards

Rob

2

You need to define the namespace gml

3

Could you give me an example. not had much experience with XML syntax

Regards

Rob

4

Here is an example of defining and using the namespace. You can adapt it to your actual query. Copy and paste this to an SSMS query window and run to see what it does.

DECLARE @x XML = 
'<gml:Envelope xmlns:gml="http://somenamespace" srsName="urn:ogc:def:crs:EPSG::27700">
  <gml:lowerCorner>0 0</gml:lowerCorner>
  <gml:upperCorner>1225000 660000</gml:upperCorner>
</gml:Envelope>'

;WITH xmlnamespaces ('http://somenamespace' AS gml)
SELECT 
	Envelope.value('./@srsName[1]','varchar(255)') as 'srsName'
FROM
	@x.nodes('//gml:Envelope') as x(Envelope)