Server解析GPX文件

Question

我正在尝试解析SQLServer2019中的一个GPX文件，但我认为名称空间遇到了一个障碍。

根据我所看到的-如果GPX文件包含：

xmlns="http://www.topografix.com/GPX/1/1"

SQL返回NULL。但是如果我从GPX文件中删除它，SQL将返回一个字符串--就像预期的那样。

SQL代码：

DECLARE @XML TABLE (XML_COLUMN XML)

DECLARE @sqlstmt NVARCHAR(255)
DECLARE @file NVARCHAR(255) = 'd:\demo_2.gpx'

SET @sqlstmt= 'SELECT * FROM OPENROWSET (BULK ''' + @file + ''', SINGLE_CLOB) AS xmlData'

INSERT INTO @XML 
    EXEC (@sqlstmt)

;WITH XMLNAMESPACES ('http://www.topografix.com/GPX/1/1' AS ns), X_CTE AS
(
    SELECT
        T1.Name.query('.') AS Name,
        T2.X_Content.query('.') AS X_Content
    FROM   
        @XML AS X
    CROSS APPLY 
        XML_Column.nodes('/gpx/trk') AS T1(Name)
    CROSS APPLY 
        XML_Column.nodes('/gpx/trk/trkseg/trkpt') AS T2(X_Content)
),
XML_Data AS
(
    SELECT
        X_Content.value('(/trkpt/@lat)[1]', 'VARCHAR(50)') AS LAT,
        X_Content.value('(/trkpt/@lon)[1]', 'VARCHAR(50)') AS LON
    FROM 
        X_CTE
)
SELECT 
    STUFF((SELECT '[' + LON + ',' + LAT + ']' + ','
           FROM XML_Data
           WHERE 1 = 1
           FOR XML PATH('')), 1, 0, '') AS mapString;

GPX文件内容(demo_2.gpx)

<?xml version="1.0" encoding="UTF-8"?>
<gpx creator="Garmin Connect" version="1.1"
  xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/11.xsd"
  xmlns:ns3="http://www.garmin.com/xmlschemas/TrackPointExtension/v1"
  xmlns="http://www.topografix.com/GPX/1/1"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns2="http://www.garmin.com/xmlschemas/GpxExtensions/v3">
  <metadata>
    <link href="connect.garmin.com">
      <text>Garmin Connect</text>
    </link>
    <time>2022-05-29T08:37:21.000Z</time>
  </metadata>
  <trk>
    <name>My Route</name>
    <type>e_bike_mountain</type>
    <trkseg>
      <trkpt lat="54.37033147551119327545166015625" lon="-3.075514398515224456787109375">
        <ele>65.8000030517578125</ele>
        <time>2022-05-29T11:37:02.000Z</time>
        <extensions>
          <ns3:TrackPointExtension>
            <ns3:atemp>17.0</ns3:atemp>
            <ns3:hr>155</ns3:hr>
          </ns3:TrackPointExtension>
        </extensions>
      </trkpt>
      <trkpt lat="54.37033147551119327545166015625" lon="-3.075514398515224456787109375">
        <ele>65.8000030517578125</ele>
        <time>2022-05-29T11:37:03.000Z</time>
        <extensions>
          <ns3:TrackPointExtension>
            <ns3:atemp>17.0</ns3:atemp>
            <ns3:hr>155</ns3:hr>
          </ns3:TrackPointExtension>
        </extensions>
      </trkpt>
    </trkseg>
  </trk>
</gpx>

真的拔出剩下的头发的最后一点与这一个，如果有人可以帮助，那将是完全棒！

Answer 1

您的XML定义了一个默认命名空间，该名称空间应用于所有XML节点--只要没有通过前缀显式定义其他名称空间：

<gpx creator="Garmin Connect" version="1.1"
     ...
     xmlns="http://www.topografix.com/GPX/1/1"

没有别名的xmlns=声明(如xmlns:ns=...)是XML文档的默认XML名称空间。

现在，如果您在查询中使用别名定义名称空间，如下所示：

;WITH XMLNAMESPACES ('http://www.topografix.com/GPX/1/1' AS ns)

然后，还必须在所有相关的XPath查询中使用别名：

SELECT
    ...
FROM   
    @XML AS X
CROSS APPLY 
    XML_Column.nodes('/ns:gpx/ns:trk') AS T1(Name)
CROSS APPLY 
    XML_Column.nodes('/ns:gpx/ns:trk/ns:trkseg/ns:trkpt') AS T2(X_Content)

或者，将XML命名空间定义为to中的默认名称空间，这样就不需要在任何地方应用名称空间别名：

;WITH XMLNAMESPACES (DEFAULT 'http://www.topografix.com/GPX/1/1')

最后--您可以编写您的XQuery 要简单得多的--尝试如下：

WITH XMLNAMESPACES (DEFAULT 'http://www.topografix.com/GPX/1/1')
    SELECT
        LAT = XC.value('@lat', 'VARCHAR(50)'),
        LON = XC.value('@lon', 'VARCHAR(50)')
    FROM   
        @Xml AS X
    CROSS APPLY 
        XML_Column.nodes('/gpx/trk/trkseg/trkpt') AS XT(XC)

这应该返回相同的值-使用更少的代码和间接方向..。

Answer 2

排序，非常感谢marc_s -我最后的工作代码.

DECLARE @XML TABLE (XML_COLUMN XML)

    DECLARE @sqlstmt NVARCHAR(255)
    DECLARE @file NVARCHAR(255) = 'd:\erc\gpx\demo_4.gpx'

    SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''' + @file + ''', SINGLE_CLOB) AS xmlData'
 
INSERT INTO @XML 
    EXEC (@sqlstmt)

;WITH XMLNAMESPACES (DEFAULT 'http://www.topografix.com/GPX/1/1'), X_CTE AS
    (   
    SELECT
        LAT = XC.value('@lat', 'VARCHAR(50)'),
        LON = XC.value('@lon', 'VARCHAR(50)')
    FROM   
        @XML AS X
    CROSS APPLY 
        XML_Column.nodes('/gpx/trk/trkseg/trkpt') AS XT(XC)
        ),
XML_Data AS
(
    SELECT * FROM X_CTE
)
    SELECT Stuff
    ((
        SELECT
        '[' + LON + ',' + LAT + ']' + ','
        FROM XML_Data
        WHERE 1 = 1
    FOR XML PATH('')), 1, 0, '') AS mapString;