How to query XML with namespace in T-SQL?

Question

I'm having difficulty querying this XML with a namespace. I can query the xml without the namespace fine.

Below is my attempt. It results in 0 records.

;WITH XMLNAMESPACES ('http://www.google.com/kml/ext/2.2' as gx)
,CTE AS
( SELECT CONVERT(XML,'<?xml version=''1.0'' encoding=''UTF-8''?>
<kml xmlns=''http://www.opengis.net/kml/2.2'' xmlns:gx=''http://www.google.com/kml/ext/2.2''>
    <Document>
        <Placemark>
            <open>1</open>
            <gx:Track>
                <altitudeMode>clampToGround</altitudeMode>
                <when>2017-10-26T11:42:05Z</when>
                <gx:coord>Lat Long Altitude</gx:coord>
                <when>2017-10-26T11:41:40Z</when>
                <gx:coord>Lat Long Altitude</gx:coord>
            </gx:Track>
        </Placemark>
    </Document>
</kml>'
        ) AS BulkColumnXML
)
SELECT altitudeModetext.node.value('.','NVARCHAR(255)') AS altitudeMode,
       gdcoordtext.node.value('.','NVARCHAR(255)') AS gdcoord,
       whentext.node.value('.','NVARCHAR(255)') AS [when]
FROM CTE
CROSS APPLY BulkColumnXML.nodes('/kml/Document/Placemark/gx:Track') as kmlDocumentPlacemarkopengxtrack(node)
CROSS APPLY kmlDocumentPlacemarkopengxtrack.node.nodes('altitudeMode/text()') as altitudeModetext(node)
CROSS APPLY kmlDocumentPlacemarkopengxtrack.node.nodes('gx:coord/text()') as gdcoordtext(node)
CROSS APPLY kmlDocumentPlacemarkopengxtrack.node.nodes('when/text()') as whentext(node)

Corrected Code by adding default namespace into with namespaces clause:

;WITH XMLNAMESPACES ('http://www.google.com/kml/ext/2.2' as gx,
                     DEFAULT 'http://www.opengis.net/kml/2.2')
,CTE AS
( SELECT CONVERT(XML,'<?xml version=''1.0'' encoding=''UTF-8''?>
<kml xmlns=''http://www.opengis.net/kml/2.2'' xmlns:gx=''http://www.google.com/kml/ext/2.2''>
    <Document>
        <Placemark>
            <open>1</open>
            <gx:Track>
                <altitudeMode>clampToGround</altitudeMode>
                <when>2017-10-26T11:42:05Z</when>
                <gx:coord>Lat Long Altitude</gx:coord>
                <when>2017-10-26T11:41:40Z</when>
                <gx:coord>Lat Long Altitude</gx:coord>
            </gx:Track>
        </Placemark>
    </Document>
</kml>'
        ) AS BulkColumnXML
)
SELECT altitudeModetext.node.value('.','NVARCHAR(255)') AS altitudeMode,
       gdcoordtext.node.value('.','NVARCHAR(255)') AS gdcoord,
       whentext.node.value('.','NVARCHAR(255)') AS [when]
FROM CTE
CROSS APPLY BulkColumnXML.nodes('/kml/Document/Placemark/gx:Track') as kmlDocumentPlacemarkopengxtrack(node)
CROSS APPLY kmlDocumentPlacemarkopengxtrack.node.nodes('altitudeMode/text()') as altitudeModetext(node)
CROSS APPLY kmlDocumentPlacemarkopengxtrack.node.nodes('gx:coord/text()') as gdcoordtext(node)
CROSS APPLY kmlDocumentPlacemarkopengxtrack.node.nodes('when/text()') as whentext(node)

You're not defining the default namespace. Stick in DEFAULT 'http://www.opengis.net/kml/2.2'. — Jeroen Mostert
– Jeroen Mostert, Commented Oct 26, 2017 at 14:03
yes, that worked! thanks, I have updated the corrected code in the question — Danny Rancher
– Danny Rancher, Commented Oct 26, 2017 at 14:07
@DannyRancher Why don't you add the correct code as an accepted answer? — Steve Ford
– Steve Ford, Commented Oct 26, 2017 at 14:15
There is still some errors in querying resulting in duplication. I'm still working on it. — Danny Rancher
– Danny Rancher, Commented Oct 26, 2017 at 14:19

Gottfried Lesigang · Accepted Answer · 2017-10-26 21:16:03Z

My magic crystal ball tells me, that you might be looking for something like this:

;WITH XMLNAMESPACES ('http://www.google.com/kml/ext/2.2' as gx,
                     DEFAULT 'http://www.opengis.net/kml/2.2')
,CTE AS
( SELECT CONVERT(XML,'<?xml version=''1.0'' encoding=''UTF-8''?>
<kml xmlns=''http://www.opengis.net/kml/2.2'' xmlns:gx=''http://www.google.com/kml/ext/2.2''>
    <Document>
        <Placemark>
            <open>1</open>
            <gx:Track>
                <altitudeMode>clampToGround</altitudeMode>
                <when>2017-10-26T11:42:05Z</when>
                <gx:coord>Lat Long Altitude</gx:coord>
                <when>2017-10-26T11:41:40Z</when>
                <gx:coord>Lat Long Altitude</gx:coord>
            </gx:Track>
        </Placemark>
    </Document>
</kml>'
) AS BulkColumnXML
)
,intermediateCTE AS
(
    SELECT CTE.BulkColumnXML.value('(/kml/Document/Placemark/open/text())[1]','NVARCHAR(255)') AS placemark_open,
           CTE.BulkColumnXML.value('(/kml/Document/Placemark/gx:Track/altitudeMode/text())[1]','nvarchar(255)') AS AltitudeMode,
           CTE.BulkColumnXML.query('/kml/Document/Placemark/gx:Track/*[local-name()!="altitudeMode"]') AS SubTree
    FROM CTE
)
,AllWhens AS 
(
SELECT intermediateCTE.placemark_open
      ,intermediateCTE.AltitudeMode
      ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS WhenIndex
      ,whn.value('text()[1]','datetime') AS WhenValue
FROM intermediateCTE
CROSS APPLY SubTree.nodes('/*:when') AS A(whn)
)
,AllCoords AS 
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS CoordIndex
      ,crd.value('text()[1]','varchar(255)') AS CoordValue
FROM intermediateCTE
CROSS APPLY SubTree.nodes('/*:coord') AS A(crd)
)
SELECT AllWhens.*
      ,AllCoords.CoordValue
FROM AllWhens
INNER JOIN AllCoords ON WhenIndex=CoordIndex

The result

    AltitudeMode    Inx WhenValue               CoordValue
----------------------------------------------------------------
1   clampToGround   2   2017-10-26 11:41:40.000 Lat Long Altitude
1   clampToGround   1   2017-10-26 11:42:05.000 Lat Long Altitude

Collectives™ on Stack Overflow

How to query XML with namespace in T-SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related