时间戳

首页 > 解决方案 > 将 XML 数据插入到具有多个节点的 SQL 表中

sql - 将 XML 数据插入到具有多个节点的 SQL 表中

问题描述

所以我有以下 XML：

     <NIACList>
<NIAC>
        <Number></Number>
        <SubmissionDate></SubmissionDate>
        <ExpirationDate  />
        <IssuerIDNO></IssuerIDNO>
        <IssuerName></IssuerName>
        <SuspensionPeriod/>
        <Cessation>
          <Basis  />
          <Date  />
        </Cessation>
        <Merchant>
          <IDNx></IDNx>
          <Name></Name>
          <Address>
            <Region></Region>
            <Locality></Locality>
            <Street></Street>
            <House></House>
            <Block  />
            <Flat  />
            <Phone  />
            <Fax  />
            <Email  />
          </Address>
        </Merchant>
        <CommercialUnit>
          <IDNx  />
          <Name  />
          <Type></Type>
          <Area></Area>
          <Location></Location>
          <Address>
            <Region></Region>
            <Locality></Locality>
            <Street></Street>
            <House></House>
            <Block  />
            <Flat  />
          </Address>
          <Activities>
            <Activity>
              <Code></Code>
              <Name></Name>
            </Activity>
          </Activities>
          <Goods>
            <Good>
              <Name></Name>
            </Good>
          </Goods>
          <WorkProgram  />
          <PublicSupplyUnit>
            <Capacity  />
            <TerraceCapacity  />
          </PublicSupplyUnit>
          <TradingAlcohol  />
          <TradingBeer  />
          <TradingTobaccoProducts  />
          <AmbulatoryTrading  />
          <MobileUnitTrading></MobileUnitTrading>
          <MobileUnit>
            <Type  />
            <Length  />
            <Width  />
            <Height  />
          </MobileUnit>
          <CommercialApparatusTrading></CommercialApparatusTrading>
          <CommercialApparatus>
            <Count  />
            <Length  />
            <Width  />
            <Height  />
          </CommercialApparatus>
        </CommercialUnit>
        <Modifications  />
      </NIAC>
</NIACList>

这是我创建的表的脚本：

    create table Merchant (
        IdMerchant int identity  primary key,
        IDNX nvarchar(max) null,
        Name nvarchar(max) null,
        WorkProgram datetime2 null,
        IdAddress int 
        );
    create table Address (
    IdAddress int identity  primary key,
    Region nvarchar(60) null,
    Locality nvarchar(50) null,
    Street nvarchar (60) null,
    House nvarchar (10) null,
    Block nvarchar (10) null,
    Flat nvarchar(10) null,
    Phone nvarchar(30) null,
    Fax nvarchar(60) null,
    Email nvarchar(60) null
    
    );

create table CommercialUnit (
IDCommercialUt int identity primary key,
IDNx nvarchar(90) null,
Name nvarchar(90) null,
Type nvarchar(90) null,
Area int null,
Location nvarchar(50) null,
TerraceCapacity float null,
TradingAlcohol bit null,
TradingBeer bit null,
TradingTobaccoProducts bit null,
AmbulatoryTrading bit null,
MobileUnitTrading bit null,
CommercialApparatusTrading bit null,
IDActivities int ,
IDGoods int ,
IDMobileUnit int ,
IDCommercial int ,
IDPSU int 
);

我不太擅长 XML，但问题是：我有表Merchant和Address. 问题是，节点地址重复了 2 次（在Merchant和CommercialUnit节点中），并且有不同的数据。我的任务是以某种方式指定插入，所以我要插入的数据将分为2类，一类用于Merchant节点，另一类用于CommercialUnit。插入Address后，记录必须与Foreign Key from Merchantand CommercialUnit（IdAddress）链接，所以数据也会被插入到这里。

我试图插入数据，但它是从 CommercialUnit 节点插入的。下面是插入的代码：

INSERT INTO Address(Region,Locality,Street,House,Block,Flat,Phone,Fax,Email)
        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') Address(c)

标签： sqlsql-serverxml

解决方案

您需要INSERT依次执行四个语句：

  1. INSERT INTO Address ...（对于商家），并将其新生成的身份值捕获到变量中。
  2. INSERT INTO Merchant ...并使用上面的变量。
  3. INSERT INTO Address ...（对于 CommercialUnit），并将其新生成的标识值捕获到一个变量中。
  4. INSERT INTO CommercialUnit ...并使用上面的变量。

概念 SQL

DECLARE @IdAddress INT;

-- Merchant
INSERT INTO Address ...

-- get last IDENTITY value for the Address table
SET @IdAddress = SCOPE_IDENTITY();

INSERT INTO Merchant ...

-- CommercialUnit
INSERT INTO Address ...

-- get last IDENTITY value for the Address table
SET @IdAddress = SCOPE_IDENTITY();

INSERT INTO CommercialUnit ...

推荐阅读