如何将XML全量导入SQL？解决仅插入第一条记录的问题

使用语句c.value('(rep_Operation/@OpNumber)[1]')仅能将XML文件的第一条记录插入SQL表，如何实现插入文件中剩余的所有记录？当前尝试的SQL代码如下：

SELECT 
    CAST(LEFT(c.value('(Customer/Meters/WaterMeter/@ReadingDate)[1]', 'varchar(100)'), 10) AS date) AS data1,
    CAST(c.value('(Customer/@PostalCode)[1]', 'varchar(100)') AS int) AS Cliente,
    CAST(c.value('(Customer/Meters/WaterMeter/WaterMeterData/@Index)[1]', 'decimal(18, 2)') AS int) AS Leitura
FROM 
    XmlFile 
CROSS APPLY 
    xmlData.nodes('(/ActarisEasyRoutes/Route/RouteData/Customers)') AS t(c)
UNION
SELECT 
    CAST(LEFT(c.value('(Customer/Meters/WaterMeter/@ReadingDate)[2]', 'varchar(100)'), 10) AS date) AS data1,
    CAST(c.value('(Customer/@PostalCode)[2]', 'varchar(100)') AS int) AS Cliente,
    CAST(c.value('(Customer/Meters/WaterMeter/WaterMeterData/@Index)[2]', 'decimal(18, 2)') AS int) AS Leitura
FROM 
    XmlFile 
CROSS APPLY 
    xmlData.nodes('(/ActarisEasyRoutes/Route/RouteData/Customers)') AS t(c)

对应的XML包含ActarisEasyRoutes、Route、Customers等多级嵌套节点。

你当前用UNION手动指定索引的方式扩展性极差，正确做法是通过多次CROSS APPLY nodes()方法逐层拆解XML嵌套节点，让每条记录自动生成一行数据，无需硬编码索引。

假设你的XML结构大致如下：

<ActarisEasyRoutes>
  <Route>
    <RouteData>
      <Customers>
        <Customer PostalCode="12345">
          <Meters>
            <WaterMeter ReadingDate="2024-01-01">
              <WaterMeterData Index="123.45" />
            </WaterMeter>
            <!-- 更多WaterMeter节点 -->
          </Meters>
        </Customer>
        <!-- 更多Customer节点 -->
      </Customers>
    </RouteData>
  </Route>
</ActarisEasyRoutes>

优化后的SQL查询 #

SELECT 
    -- 无需指定[1]，当前行已对应单个WaterMeter节点
    CAST(LEFT(wm.value('@ReadingDate', 'varchar(100)'), 10) AS date) AS data1,
    CAST(c.value('@PostalCode', 'varchar(100)') AS int) AS Cliente,
    CAST(wmd.value('@Index', 'decimal(18, 2)') AS int) AS Leitura
FROM 
    XmlFile 
-- 第一步：拆解到每个Customer节点
CROSS APPLY 
    xmlData.nodes('/ActarisEasyRoutes/Route/RouteData/Customers/Customer') AS t(c)
-- 第二步：从每个Customer拆解到对应的WaterMeter节点
CROSS APPLY 
    c.nodes('Meters/WaterMeter') AS t2(wm)
-- 第三步：拆解每个WaterMeter下的WaterMeterData节点
CROSS APPLY 
    wm.nodes('WaterMeterData') AS t3(wmd)

关键要点 #

• 避免硬编码[1]、[2]这类索引，而是通过nodes()把每个嵌套子节点拆成独立行。
• 每一层nodes()返回的结果集对应一个XML子节点，后续直接用当前节点的value()读取属性即可。
• 如果Customer下只有一个WaterMeter，或WaterMeter下只有一个WaterMeterData，可合并拆解步骤：

  SELECT 
      -- 用../@ReadingDate向上一级节点读取属性
      CAST(LEFT(wmd.value('../@ReadingDate', 'varchar(100)'), 10) AS date) AS data1,
      CAST(c.value('@PostalCode', 'varchar(100)') AS int) AS Cliente,
      CAST(wmd.value('@Index', 'decimal(18, 2)') AS int) AS Leitura
  FROM 
      XmlFile 
  CROSS APPLY 
      xmlData.nodes('/ActarisEasyRoutes/Route/RouteData/Customers/Customer') AS t(c)
  CROSS APPLY 
      c.nodes('Meters/WaterMeter/WaterMeterData') AS t2(wmd)
  

内容的提问来源于stack exchange，提问作者José Alexandre Barbosa Gomes