含重复Address节点的XML数据插入关联SQL表的技术问询

看起来你之前踩了XPath路径定位不精准的坑，导致误把CommercialUnit的Address数据插到了Merchant对应的地址里。咱们需要分开处理Merchant和CommercialUnit各自的Address节点，并且通过外键把它们正确关联到对应的表中。下面是分步的解决方案：

步骤1：插入Merchant的Address并关联到Merchant表 #

我们可以用OUTPUT子句捕获插入Address表后生成的自增IdAddress，再把这个ID和Merchant的其他字段一起插入到Merchant表，确保关联关系准确：

-- 声明临时表存储插入的Address ID和对应的Merchant数据
DECLARE @MerchantAddresses TABLE (IdAddress INT, IDNx NVARCHAR(MAX), Name NVARCHAR(MAX));

-- 插入Merchant的Address数据，同时捕获生成的IdAddress和Merchant的IDNx、Name
INSERT INTO Address(Region, Locality, Street, House, Block, Flat, Phone, Fax, Email)
OUTPUT inserted.IdAddress, c.value('../IDNx[1]', 'NVARCHAR(MAX)'), c.value('../Name[1]', 'NVARCHAR(MAX)')
INTO @MerchantAddresses
SELECT 
    Region = c.value('Region[1]', 'NVARCHAR(60)'),
    Locality = c.value('Locality[1]', 'NVARCHAR(50)'),
    Street = c.value('Street[1]', 'NVARCHAR(60)'),
    House = c.value('House[1]', 'NVARCHAR(10)'),
    Block = c.value('Block[1]', 'NVARCHAR(10)'),
    Flat = c.value('Flat[1]', 'NVARCHAR(10)'),
    Phone = c.value('Phone[1]', 'NVARCHAR(30)'),
    Fax = c.value('Fax[1]', 'NVARCHAR(60)'),
    Email = c.value('Email[1]', 'NVARCHAR(60)')
FROM @xml.nodes('/NIACList/NIAC/Merchant/Address') AS Address(c);

-- 将Merchant数据插入到Merchant表，关联刚才获取的IdAddress
INSERT INTO Merchant(IDNX, Name, IdAddress)
SELECT IDNx, Name, IdAddress
FROM @MerchantAddresses;

步骤2：插入CommercialUnit的Address并关联到CommercialUnit表 #

用同样的思路，先插入CommercialUnit的Address，捕获对应的IdAddress，再插入CommercialUnit表：

-- 声明临时表存储插入的Address ID和对应的CommercialUnit数据
DECLARE @CommercialUnitAddresses TABLE (
    IdAddress INT, 
    IDNx NVARCHAR(90), 
    Name NVARCHAR(90), 
    Type NVARCHAR(90), 
    Area INT, 
    Location NVARCHAR(50), 
    TerraceCapacity FLOAT, 
    TradingAlcohol BIT, 
    TradingBeer BIT, 
    TradingTobaccoProducts BIT, 
    AmbulatoryTrading BIT, 
    MobileUnitTrading BIT, 
    CommercialApparatusTrading BIT
);

-- 插入CommercialUnit的Address数据，捕获IdAddress和CommercialUnit的其他字段
INSERT INTO Address(Region, Locality, Street, House, Block, Flat, Phone, Fax, Email)
OUTPUT inserted.IdAddress, 
       c.value('../IDNx[1]', 'NVARCHAR(90)'),
       c.value('../Name[1]', 'NVARCHAR(90)'),
       c.value('../Type[1]', 'NVARCHAR(90)'),
       c.value('../Area[1]', 'INT'),
       c.value('../Location[1]', 'NVARCHAR(50)'),
       c.value('../PublicSupplyUnit/TerraceCapacity[1]', 'FLOAT'),
       c.value('../TradingAlcohol[1]', 'BIT'),
       c.value('../TradingBeer[1]', 'BIT'),
       c.value('../TradingTobaccoProducts[1]', 'BIT'),
       c.value('../AmbulatoryTrading[1]', 'BIT'),
       c.value('../MobileUnitTrading[1]', 'BIT'),
       c.value('../CommercialApparatusTrading[1]', 'BIT')
INTO @CommercialUnitAddresses
SELECT 
    Region = c.value('Region[1]', 'NVARCHAR(60)'),
    Locality = c.value('Locality[1]', 'NVARCHAR(50)'),
    Street = c.value('Street[1]', 'NVARCHAR(60)'),
    House = c.value('House[1]', 'NVARCHAR(10)'),
    Block = c.value('Block[1]', 'NVARCHAR(10)'),
    Flat = c.value('Flat[1]', 'NVARCHAR(10)'),
    Phone = NULL, -- CommercialUnit的Address节点没有这些字段，设为NULL
    Fax = NULL,
    Email = NULL
FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/Address') AS Address(c);

-- 插入CommercialUnit表，关联对应的IdAddress
INSERT INTO CommercialUnit(
    IDNx, Name, Type, Area, Location, TerraceCapacity, 
    TradingAlcohol, TradingBeer, TradingTobaccoProducts, 
    AmbulatoryTrading, MobileUnitTrading, CommercialApparatusTrading, 
    IdAddress
)
SELECT 
    IDNx, Name, Type, Area, Location, TerraceCapacity, 
    TradingAlcohol, TradingBeer, TradingTobaccoProducts, 
    AmbulatoryTrading, MobileUnitTrading, CommercialApparatusTrading, 
    IdAddress
FROM @CommercialUnitAddresses;

关键注意事项 #

• 精准的XPath路径：一定要确保nodes()方法里的路径分别是/NIACList/NIAC/Merchant/Address和/NIACList/NIAC/CommercialUnit/Address，这是你之前出错的核心原因。
• OUTPUT子句的优势：通过临时表捕获自增的IdAddress，能保证地址和对应实体的关联绝对准确，避免后续查询可能带来的不一致问题。
• 字段匹配：CommercialUnit的Address节点没有Phone、Fax、Email字段，插入时要显式设为NULL，避免因字段不匹配报错。

内容的提问来源于stack exchange，提问作者markossw