XML query

Question

XML query

SSCrazy

Points: 2864

June 23, 2020 at 10:56 am

Firstly apologises as I suspect I'm missing the obvious somewhere. I can query other XML files successfully but not this one. My code is:

declare @xml xml

set @xml='<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<name>overpass-turbo.eu export</name>
<description>Filtered OSM data converted to KML by overpass turbo.
Copyright: The data included in this document is from www.openstreetmap.org. The data is made available under ODbL.
Timestamp: 2020-06-22T13:12:02Z</description>
</Document>
</kml>'


--SELECT @xml=bulkcolumn FROM OPENROWSET (BULK 'C:\test\exporttest.xml', SINGLE_CLOB) AS xCol;

SELECT
T.c.value ('name[1]','varchar (500)') AS name
FROM   @xml.nodes('/kml/Document') T(c)

This is a reduced version of the XML but I was having the same problem with the full version.

It returns no results rather than the name and I can't work out why? I've tried several different path and element combinations and each return no results. I also tried selecting the description but I get the same issue.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Try this:

SELECT name = T.c.value('(*:name)[1]', 'varchar (500)')
FROM @xml.nodes('/*:kml/*:Document') T(c);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 2

You have to take account of the namespace in the XML - it's like a qualifier

So you can either alias the namespace

with xmlnamespaces ('http://www.opengis.net/kml/2.2' as kmlns)
SELECT
T.c.value ('(kmlns:name)[1]','varchar (500)') AS name
FROM   @xml.nodes('/kmlns:kml/kmlns:Document') T(c);

or use a wildcard to ignore any particular namespace (like Phil showed)

SELECT
T.c.value ('(*:name)[1]','varchar (500)') AS name
FROM   @xml.nodes('/*:kml/*:Document') T(c);

or a sledgehammer approach is to set the default namespace

with xmlnamespaces (default 'http://www.opengis.net/kml/2.2')
SELECT
T.c.value ('name[1]','varchar (500)') AS name
FROM   @xml.nodes('/kml/Document') T(c)

as_1234 SSCrazy Points: 2864 More actions · Answer 3

Edit: I was writing as Kev Riley posted and my questions were answered in his post.

Both: Thank you for your help.

Original post:

That worked, thank you. Am I right in thinking that the * means 'any namespace'? Sorry I searched and couldn't find an answer.

When your post made me realise that the namespace was the issue I did some more searching. In case it helps anyone else it looks like you can specify a default namespace as follows:

;WITH XMLNAMESPACES (DEFAULT 'http://www.opengis.net/kml/2.2')

SELECT
T.c.value ('name[1]','varchar (500)') AS name
FROM   @xml.nodes('kml/Document') T(c)

The reason I say "looks like" is because it works in my example but I'm learning so I'm cautious of providing incorrect information.

Thanks

This reply was modified 4 years, 5 months ago by as_1234. Reason: I was replying at the same time as someone else
This reply was modified 4 years, 5 months ago by as_1234.