XML query

• as_1234

  SSCrazy

  Points: 2875

  More actions

  June 23, 2020 at 10:56 am

  #3764774

  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.

   

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  June 23, 2020 at 1:33 pm

  #3764797

  Try this:

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

  ---

• Kev Riley

  SSCrazy Eights

  Points: 9075

  More actions

  June 23, 2020 at 2:13 pm

  #3764799

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

  More actions

  June 23, 2020 at 2:27 pm

  #3764802

  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 5 years, 1 month ago by as_1234. Reason: I was replying at the same time as someone else
  • This reply was modified 5 years, 1 month ago by as_1234.