时间戳

首页 > 解决方案 > 将 XML 多个嵌套节点插入 SQL 表

sql-server - 将 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>

基于这个 XML 结构，我在 MS SQL 中创建了一个带有以下表脚本的数据库：

create table NIAC (
IDNIAC int identity primary key,
Number nvarchar(80) null,
SubmissionDate datetime2 null,
ExpirationDate datetime2 null,
IssuerIDNO  nvarchar(max) null,
IssuerName  nvarchar(60) null,
SuspensionPeriod datetime2 null,
Modifications nvarchar(60) null
);

create table Cessation (
IdCessation int identity primary key,
Basis nvarchar(60) null,
Date date null,
IDNIAC int 
);


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(max) null,
TerraceCapacity float null,
TradingAlcohol bit null,
TradingBeer bit null,
TradingTobaccoProducts bit null,
AmbulatoryTrading bit null,
MobileUnitTrading bit null,
CommercialApparatusTrading bit null,
IdAddress int,
IDActivities int ,
IDGoods int ,
IDMobileUnit int ,
IDCommercial int ,
IDPSU int 
);


create table Activities (
IDActivities int identity  primary key,
Code nvarchar (90) null,
Name nvarchar (90) null
);

create table Goods(
IDGoods int identity  primary key,
Name nvarchar(60) null
);

create table MobileUnit(
IDMobileUnit int identity  primary key,
Type numeric null,
Length numeric null,
Width numeric null,
Height numeric null
);

create table CommercialApparatus(
IDCommercial int identity primary key,
Count float null,
Length numeric null,
Width numeric null,
Height numeric null
);


create table PublicSupplyUnit(
IDPSU int identity  primary key,
Capacity float,
TerraceCapacity float 
);


create table NIACList(
ID int identity primary key,
IDNIAC int not null,
IDNIACList int 
);


create table NIACToComponents(
IDNIACToComponents int identity primary key,
IDNIAC int,
IDAddressMerchant int,
IDAddressCU int,
IdCessation int,
IdMerchant int,
IDCommercialUt int,
IDActivities int,
IDGoods int,
IDMobileUnit int,
IDCommercial int,
IDPSU int,

);




drop table if exists NIAC;

drop table if exists NIACList;

drop table if exists Cessation;

drop table if exists Merchant;

drop table if exists Address;

drop table if exists CommercialApparatus;

drop table if exists CommercialUnit;

drop table if exists Goods;

drop table if exists MobileUnit;

drop table if exists  PublicSupplyUnit;

drop table if exists  Activities;

drop table if exists NIACToComponents;



alter table dbo.NIACList
add constraint FK_ID_NIAC 
foreign key (IDNIAC) references dbo.NIAC(IDNIAC)
on delete cascade
on update cascade;

alter table dbo.Cessation
Add constraint FK_IDNIAC
foreign key(IDNIAC) references dbo.NIAC(IDNIAC)
on delete cascade
on update cascade;

alter table dbo.Merchant
add constraint FK_IdAdress
foreign key(IdAddress) references dbo.Address(IdAddress)
on delete cascade
on update cascade;

alter table dbo.CommercialUnit
add constraint FK_IdAddress
foreign key(IdAddress) references dbo.Address(IdAddress)
on delete cascade
on update cascade;


alter table dbo.CommercialUnit
add constraint FK_Activities
foreign key(IDActivities) references dbo.Activities(IDActivities)
on delete cascade
on update cascade;

alter table dbo.CommercialUnit
add constraint FK_IDGoods
foreign key(IDGoods) references dbo.Goods(IDGoods)
on delete cascade
on update cascade;

alter table dbo.CommercialUnit
add constraint FK_IDMobileUnit
foreign key(IDMobileUnit) references dbo.MobileUnit(IDMobileUnit)
on delete cascade
on update cascade;

alter table dbo.CommercialUnit
add constraint FK_IDCommercial 
foreign key(IDCommercial) references dbo.CommercialApparatus(IDCommercial)
on delete cascade
on update cascade;

alter table dbo.CommercialUnit
add constraint FK_IDPSU
foreign key(IDPSU) references dbo.PublicSupplyUnit(IDPSU)
on delete cascade
on update cascade;

假设有很多NIACinto NIACList（50-100 个节点），我想从我的 XML 文件中插入数据。我INSERT只对一个进行了操作NIAC，使用 3 个过程（2 个用于插入表，1 个用于调用两个过程并将插入数据的表的所有 ID 插入另一个表，因此所有数据都将被链接给 NIAC）。

将数据插入表的脚本如下： 1 个过程：

ALTER procedure [dbo].[InsertXMLMultipleTables]
(
@idAddress1 int out,
@idAddress2 int out,
@idactivities int out,
@idgoods int out,
@idmobileunit int out,
@idcommercial int out,
@idcommercialut int out,
@idpsu int out,
@idmerchant int out,
@xml xml
)
as 
set @xml = *


begin 
set nocount on

INSERT INTO Activities(Code,Name)
        SELECT  
    
Code=c.value('Code[1]','nvarchar(90)') ,
Name=c.value('Name[1]','nvarchar(90)') 
FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/Activities/Activity') Activities(c)
set @IDActivities=SCOPE_IDENTITY();
end;




begin
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)
    set @idAddress1=SCOPE_IDENTITY();
    end;

    begin
    INSERT INTO Merchant(IDNX,Name,WorkProgram,IdAddress)
        SELECT 
        Region=c.value('IDNx[1],','nvarchar(max)'),
        Locality=c.value('Name[1],','nvarchar(max)') ,
        Street=c.value('WorkProgram[1],','datetime2') ,
        @idAddress1
    FROM @xml.nodes('NIACList/NIAC/Merchant') Merchant(c)
    set @idmerchant=SCOPE_IDENTITY();
    end;

    
begin
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/CommercialUnit/Address') Address(c)
    set @idAddress2=SCOPE_IDENTITY();
    set @idcommercialut=SCOPE_IDENTITY();

    end;


    begin
    INSERT INTO CommercialApparatus(Count,Length,Width,Height )
        SELECT      
    Count =c.value('Type[1]','int') ,
        Length=c.value('Length[1]','int') ,
         Width=c.value('Width[1]','int') ,
         Height=c.value('Height[1]','int') 
    FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/CommercialApparatus') CommercialApparatus(c)
    set @idcommercial=SCOPE_IDENTITY();
    end;




    begin
    INSERT INTO Goods(Name)
        SELECT 
        Name=c.value('Name[1]','nvarchar(60)') 
    FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/Goods/Good') Goods(c)
    set @idgoods=SCOPE_IDENTITY();
    end;

    begin
    INSERT INTO MobileUnit(Type,Length,Width,Height )
        SELECT  
    Type =c.value('Type[1]','int') ,
        Length=c.value('Length[1]','int') ,
         Width=c.value('Width[1]','int') ,
         Height=c.value('Height[1]','int') 
    FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/MobileUnit') MobileUnit(c)
         set @idmobileunit=SCOPE_IDENTITY();
    end;



    begin
    INSERT INTO PublicSupplyUnit(Capacity,TerraceCapacity)
        SELECT 
        Capacity=c.value('Capacity[1]','int') ,
        TerraceCapacity=c.value('TerraceCapacity[1]','int')
    FROM @xml.nodes('/NIACList/NIAC/CommercialUnit/PublicSupplyUnit') PublicSupplyUnit(c)
    set @idpsu=SCOPE_IDENTITY();
    end;



    begin
    INSERT INTO CommercialUnit(IDNX,Name,Type,Area,Location,TerraceCapacity,TradingAlcohol,TradingBeer,TradingTobaccoProducts,AmbulatoryTrading,MobileUnitTrading,CommercialApparatusTrading,IDActivities,IdAddress,IDCommercial,IDGoods,IDMobileUnit,IDPSU)
        SELECT 
        IDNx=c.value('IDNx[1],','nvarchar(90)'),
        Name=c.value('Name[1],','nvarchar(90)') ,
        Type=c.value('Type[1],','nvarchar(90)') ,
        Area=c.value('Area[1],','int') ,
        Location=c.value('Location[1],','nvarchar(max)'),
        TerraceCapacity=c.value('TerraceCapacity[1],','float') ,
        TradingAlcohol=c.value('TradingAlcohol[1],','bit') ,
        TradingBeer=c.value('TradingBeer[1],','bit') ,
        TradingTobaccoProducts=c.value('TradingTobaccoProducts[1],','bit'),
        AmbulatoryTrading=c.value('AmbulatoryTrading[1],','bit') ,
        MobileUnitTrading=c.value('MobileUnitTrading[1],','bit') ,
        CommercialApparatusTrading=c.value('CommercialApparatusTrading[1],','bit') ,
        @idactivities,@idAddress2,@idcommercial,@idgoods,@idmobileunit,@idpsu
    FROM @xml.nodes('NIACList/NIAC/CommercialUnit') CommercialUnit(c)
    end;

2程序：

ALTER procedure [dbo].[InsertXMLNIAC]
(
@xml xml,
@idNIAC int output,
@idCessation int output
)
as 
set @xml=*

begin 
set nocount on


INSERT INTO NIAC(Number,SubmissionDate,ExpirationDate,IssuerIDNO,IssuerName,SuspensionPeriod,Modifications)
        SELECT 
        Number=c.value('Number[1],','nvarchar(80)'),
        SubmissionDate=c.value('SubmissionDate[1],','datetime2') ,
        ExpirationDate=c.value('ExpirationDate[1],','datetime2') ,
        IssuerIDNO=c.value('IssuerIDNO[1],','nvarchar(max)') ,
        IssuerName=c.value('IssuerName[1],','nvarchar(60)') ,
        SuspensionPeriod=c.value('SuspensionPeriod[1],','datetime2') ,
        Modifications=c.value('Modifications[1],','nvarchar(60)') 
    FROM @xml.nodes('NIACList/NIAC') NIAC(c)
    set @idNIAC=SCOPE_IDENTITY();
    end;

    begin
    INSERT INTO Cessation(Basis,Date,IDNIAC)
        SELECT 
        Basis=c.value('Basis[1],','nvarchar(60)'),
        Date=c.value('Date[1],','date') ,      
        @idNIAC
    FROM @xml.nodes('NIACList/NIAC/Cessation') Cessation(c)
    set @idCessation=SCOPE_IDENTITY();
    end;

3程序：

ALTER PROCEDURE [dbo].[NIACFORCOMPONENTS]
@xml1 xml
AS
BEGIN
DECLARE 
        @idAddress1 int,
        @idAddress2 int,
        @idactivities int,
        @idgoods int,
        @idmobileunit int,
        @idcommercial int,
        @idcommercialut int,
        @idpsu int,
        @idmerchant int,
        @idNIAC int,
        @idCessation int


    SET NOCOUNT ON;
    
    declare @trancount int;
    set @trancount=@@TRANCOUNT;
    BEGIN TRANSACTION

    EXEC [dbo].[InsertXMLMultipleTables]
        @idAddress1 = @idAddress1 OUTPUT,
        @idAddress2 = @idAddress2 OUTPUT,
        @IDActivities = @idactivities OUTPUT,
        @idgoods = @idgoods OUTPUT,
        @idmobileunit = @idmobileunit OUTPUT,
        @idcommercial = @idcommercial OUTPUT,
        @idcommercialut = @idcommercialut OUTPUT,
        @idpsu = @idpsu OUTPUT,
        @idmerchant = @idmerchant OUTPUT,
        @xml = @xml1


        EXEC [dbo].[InsertXMLNIAC]
        
        @idNIAC = @idNIAC OUTPUT,
        @idCessation = @idCessation OUTPUT,
        @xml=@xml1

     
        


         begin try
        if @trancount = 0
            begin transaction
        else
            save transaction NIACFORCOMPONENTS;

        

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(),
                 @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction NIACFORCOMPONENTS;

        raiserror ('NIACFORCOMPONENTS: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
    
    Begin
                              
        INSERT INTO NIACToComponents(IDNIAC,IdCessation,IdMerchant,IDAddressMerchant,IDAddressCU,IDCommercialUt,IDActivities,IDGoods,IDMobileUnit,IDCommercial,IDPSU)
        SELECT 
        @idNIAC as N'@idNIAC',
        @idCessation as N'@idCessation',
        @idmerchant as N'@idmerchant',
        @idAddress1 as N'@idAddress1',
        @idAddress2 as N'@idAddress2',
        @idcommercialut as N'@idcommercialut',
        @idactivities as N'@idactivities',
        @idgoods as N'@idgoods',
        @idmobileunit as N'@idmobileunit',
        @idcommercial as N'@idcommercial',
        @idpsu as N'@idpsu'
        
        
    
    COMMIT
        END             
END

基于这些脚本，插入非常适用于一个，表中的每个 ID 都链接到它，但我想对内部的所有节点NIAC进行这样的插入，知道每个节点的位置。NIACNIACList

标签： sql-serverxml

解决方案

您可以根据需要使用以下概念示例。

您可以INSERT通过遍历父 XML 元素将数据存入数据库表。您将需要两个 INSERT 语句。在它们中的第一个之后，您需要像今天已经在做的那样保留一个 IDENTITY 值，以便在第二个 INSERT 中用于子 XML 片段和相应的行。

SQL

DECLARE @INPUTXML XML = '<Lineitems>
  <Lineitem>
    <Ticker>TER</Ticker>
    <ID>0</ID>
    <LineItem>Net Revenue</LineItem>
    <XFundCode>TRIN</XFundCode>
    <UserID>TDP</UserID>
  </Lineitem>
  <Lineitem>
    <Ticker>TER</Ticker>
    <ID>0</ID>
    <LineItem>Cost of Revenue</LineItem>
    <XFundCode>XXP</XFundCode>
    <UserID>TDP</UserID>    
  </Lineitem>
</Lineitems>';

DECLARE @Ticker  AS VARCHAR(MAX)
   , @ID        AS INT
   , @Lineitem  AS VARCHAR(MAX)
   , @XFundCode AS VARCHAR(MAX)
   , @UserID    AS VARCHAR(MAX);

-- count total number of items
DECLARE @i INT
   , @cnt INT = @INPUTXML.value('count(/Lineitems/Lineitem)', 'INT');
SELECT @cnt;

-- loop XML item by item
SET @i = 1;
WHILE @i <= @cnt BEGIN

      SELECT @Ticker = col.value('(Ticker/text())[1]','VARCHAR(MAX)')
         , @ID = col.value('(ID/text())[1]','INT')
         , @Lineitem = col.value('(LineItem/text())[1]','VARCHAR(MAX)')
         , @XFundCode = col.value('(XFundCode/text())[1]','VARCHAR(MAX)')
         , @UserID = col.value('(UserID/text())[1]','VARCHAR(MAX)')
      FROM @INPUTXML.nodes('/Lineitems/Lineitem[position() = sql:variable("@i")]') AS tab(col)

     -- do whatever needed in the loop here

   SET @i += 1;
END

推荐阅读