如何将XML全量导入SQL？当前仅插入第一条记录

使用c.value('(rep_Operation/@OpNumber)[1]')这类写法时，仅能将XML文件中的第一条记录插入SQL表，如何修改SQL语句，实现将XML中所有客户记录插入SQL表？

XML示例 #

<?xml version="1.0" encoding="utf-8"?>
<ActarisEasyRoutes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="Route" XMLVersion="v2.3">
    <Route ID="1" Name="Route" Code="Route1" Password="" Message="">
        <RouteSettings>
            <RouteWaterMeterSettings ConsumptionControlFlag="2" MaxConsumption1="0" MaxConsumption2="0" MaxConsumption3="0" MaxConsumption4="0" Tolerance1="0" Tolerance2="0" Tolerance3="0" Tolerance4="0" Tolerance5="0" />
            <RouteHeatMeterSettings ConsumptionControlFlag="2" MaxConsumption1="0" MaxConsumption2="0" MaxConsumption3="0" MaxConsumption4="0" Tolerance1="0" Tolerance2="0" Tolerance3="0" Tolerance4="0" Tolerance5="0" DefaultFirstFrame="1" DefaultSecondFrame="2" />
            <RouteGasMeterSettings ConsumptionControlFlag="2" MaxConsumption1="0" MaxConsumption2="0" MaxConsumption3="0" MaxConsumption4="0" Tolerance1="0" Tolerance2="0" Tolerance3="0" Tolerance4="0" Tolerance5="0" CalorificFactor="0.0000" />
        </RouteSettings>
    <RouteData>
    <Customers>
        <Customer ID="1" BillingCode="" FirstName="AVENIDA CENTRAL 276 P5 ESQ TR" LastName="" Address="FLAVIA ARAUJO AFONSO" PostalCode="1337602" City="" Created="false" Modified="false">
          <Meters>
              <WaterMeter ID="1" BillingCode="" ReadingDate="2023-06-27T14:04:07" ReadingType="1" ReadingMode="1" ReadingStatus="2" NbWheels="8" EnergyType="0" UnitType="0" SerialNumber="12LA066872" ReadSerialNumber="12LA066872" SerialNumberCheck="1" FreeMessage="" FileNumber="0" Created="false" Modified="false" IsMeterHardToRead="false">
                  <WaterMeterData Index="326.629" IndexCheck="0" ConsumptionCheckFlag="-1" ConsumptionTolerance="0" ConsumptionMin="0" ConsumptionMax="0" />
              </WaterMeter>
          </Meters>
      </Customer>
      <Customer ID="2" BillingCode="" FirstName="AVENIDA CENTRAL LT243 P1 DRT FR" LastName="" Address="LILIANA SOFIA ESTEVES DA SILVA" PostalCode="1319337" City="" Created="false" Modified="false">
          <Meters>
              <WaterMeter ID="2" BillingCode="" ReadingDate="2023-06-27T14:04:07" ReadingType="1" ReadingMode="1" ReadingStatus="2" NbWheels="8" EnergyType="0" UnitType="0" SerialNumber="12LA056530" ReadSerialNumber="12LA056530" SerialNumberCheck="1" FreeMessage="" FileNumber="1" Created="false"  Modified="false" IsMeterHardToRead="false">
                  <WaterMeterData Index="538.157" IndexCheck="0" ConsumptionCheckFlag="-1" ConsumptionTolerance="0" ConsumptionMin="0" ConsumptionMax="0" />
              </WaterMeter>
          </Meters>
      </Customer>
      <Customer ID="3" BillingCode="" FirstName="AVENIDA CENTRAL LT243 P1 DRT TR" LastName="" Address="EDUARDO AUGUSTO SILVA RAMOS" PostalCode="442569" City="" Created="false" Modified="false">
          <Meters>
              <WaterMeter ID="3" BillingCode="" ReadingDate="2023-06-27T14:04:07" ReadingType="1" ReadingMode="1" ReadingStatus="2" NbWheels="8" EnergyType="0" UnitType="0" SerialNumber="12LA056557" ReadSerialNumber="12LA056557" SerialNumberCheck="1" FreeMessage="" FileNumber="2" Created="false" Modified="false" IsMeterHardToRead="false">
                  <WaterMeterData Index="213.476" IndexCheck="0" ConsumptionCheckFlag="-1" ConsumptionTolerance="0" ConsumptionMin="0" ConsumptionMax="0" />
              </WaterMeter>
          </Meters>
      </Customer>
      <Customer ID="4" BillingCode="" FirstName="AVENIDA CENTRAL LT243 P1 ESQ" LastName="" Address="ALFREDO MARTINS SOARES" PostalCode="526193" City="" Created="false" Modified="false">
          <Meters>
              <WaterMeter ID="4" BillingCode="" ReadingDate="2023-06-27T14:04:08" ReadingType="1" ReadingMode="1" ReadingStatus="2" NbWheels="8" EnergyType="0" UnitType="0" SerialNumber="12LA056553" ReadSerialNumber="12LA056553" SerialNumberCheck="1" FreeMessage="" FileNumber="3" Created="false" Modified="false" IsMeterHardToRead="false">
                  <WaterMeterData Index="151.356" IndexCheck="0" ConsumptionCheckFlag="-1" ConsumptionTolerance="0" ConsumptionMin="0" ConsumptionMax="0" />
              </WaterMeter>
          </Meters>
      </Customer>
      <Customer ID="5" BillingCode="" FirstName="AVENIDA CENTRAL LT243 P2 DRT FR" LastName="" Address="ALICE MELO COSTA" PostalCode="486647" City="" Created="false" Modified="false">
          <Meters>
              <WaterMeter ID="5" BillingCode="" ReadingDate="2023-06-27T14:04:08" ReadingType="1" ReadingMode="1" ReadingStatus="2" NbWheels="8" EnergyType="0" UnitType="0" SerialNumber="12LA056712" ReadSerialNumber="12LA056712" SerialNumberCheck="1" FreeMessage="" FileNumber="4" Created="false" Modified="false" IsMeterHardToRead="false">
                  <WaterMeterData Index="344.635" IndexCheck="0" ConsumptionCheckFlag="-1" ConsumptionTolerance="0" ConsumptionMin="0" ConsumptionMax="0" />
              </WaterMeter>
          </Meters>
      </Customer>
      <!-- and so on .... many more customers ...... -->
      </RouteData>
    </Route>
</ActarisEasyRoutes>

当前使用的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)

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)

解决方案 #

问题根源 #

当前SQL的nodes()方法定位到的是Customers节点（整个客户集合容器），而非每个独立的Customer节点。这样每次调用c.value('(Customer/xxx)[n]')只能取集合里第n个客户，无法遍历所有记录，而且重复写UNION的方式完全无法适配大量客户的场景。

修正后的SQL语句 #

SELECT 
    -- 直接读取当前Customer节点下的WaterMeter读取日期，截取前10位转成date类型
    CAST(LEFT(c.value('(Meters/WaterMeter/@ReadingDate)[1]', 'varchar(100)'), 10) AS date) AS data1,
    -- 读取当前Customer节点的PostalCode属性转成int
    CAST(c.value('@PostalCode', 'varchar(100)') AS int) AS Cliente,
    -- 读取当前Customer节点下WaterMeterData的Index属性转成int
    CAST(c.value('(Meters/WaterMeter/WaterMeterData/@Index)[1]', 'decimal(18, 2)') AS int) AS Leitura
FROM 
    XmlFile 
CROSS APPLY 
    -- 关键修改：nodes()定位到每个独立的Customer节点
    xmlData.nodes('/ActarisEasyRoutes/Route/RouteData/Customers/Customer') AS t(c)

说明 #

1. nodes()方法的定位修正：把nodes('/ActarisEasyRoutes/Route/RouteData/Customers')改成nodes('/ActarisEasyRoutes/Route/RouteData/Customers/Customer')，这样CROSS APPLY会为每个Customer节点生成一行数据，自动遍历所有客户。
2. value()方法的路径简化：因为当前上下文已经是单个Customer节点，所以路径直接写Meters/WaterMeter/@ReadingDate即可，无需再从Customer开头；读取节点属性时直接用@PostalCode就能获取当前客户的邮编。
3. 无需UNION重复查询：修正后一次查询就能取出所有客户记录，不需要手动写UNION来逐个指定第n条记录，无论XML里有多少客户都能自动适配。

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