多亲子插入 [英] Multi Parent-Child Insertion

查看：26
本文介绍了多亲子插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试编写一个存储过程来从 XML 字符串中提取信息并使用它来创建多个父子关系.我正在尝试将此 XML 推送到实际的数据库表中.基本上，本地客户端会将 XML 文件发送到数据库并将其存储为字符串.然后我需要从该字符串中提取信息并更新相应的表.如果这只是一个 Table-A 到 Table-B，这不会那么困难.我遇到的问题是它需要在适用的情况下从表 A 到表 B 到表 C 再到表 D.下面是一个示例 XML:

<比赛><姓名>每日</姓名><StartDate>11/9/2015 12:40:07 AM</StartDate><运行><Id>123</Id><名称>每日跑步</名称><RunDate>11/9/2015 12:40:07 AM</RunDate><CompetitionId>1</CompetitionId><运行记录><Id>001</Id><号码>007</号码><ElapsedTime>23.007</ElapsedTime><RunId>123</RunId></运行记录></运行><运行><Id>456</Id><名称>每日跑步</名称><RunDate>11/9/2015 12:47:07 AM</RunDate><CompetitionId>1</CompetitionId><运行记录><Id>002</Id><号码>700</号码><ElapsedTime>23.707</ElapsedTime><RunId>456</RunId><RunRecordSpecialty><Id>1</Id><差点>17</差点><TeamPoints>50000</TeamPoints><RunRecordId>002</RunRecordId></RunRecordSpecialty></运行记录></运行></比赛></RunRecordFile>

我尝试使用 DECLARED 表来保存每个创建的主键，并使用 SQL OUTPUT 来收集它们.当我运行我的 SQL 时，我得到 (0) 行更新.这是我在 SQL 中尝试过的:

CREATE PROC [dbo].[RaceFilePush]作为声明 @CompetitionIdMapping 表 ( CompetitionId bigint )声明 @RunIdMapping 表 ( RunId bigint )声明 @RunRecordIdMapping 表 ( RunRecordId bigint )开始声明 @rrXML 为 XML声明 @rrfId 为 BIGINTSET @rrfId = (SELECT TOP 1 Id FROM RunRecordFile WHERE Submitted IS NULL)SET @rrXML = (SELECT TOP 1 RaceFile FROM RunRecordFile WHERE Id = @rrfId)BEGIN TRAN 比赛开始尝试INSERT INTO 比赛(姓名，开始日期)OUTPUT INSERTED.Id INTO @CompetitionIdMapping(CompetitionId)选择xCompetition.value('(Name)[1]', 'varchar(225)') AS 名称,xCompetition.value('(StartDate)[1]', 'datetime') AS StartDate,@rrfId AS RunRecordFileId从@rrXML.nodes('/RunRecordFile/Competition') AS E(xCompetition)插入运行 (姓名,运行日期,比赛编号)OUTPUT INSERTED.Id INTO @RunIdMapping(RunId)选择xRuns.value('(Name)[1]','varchar(80)') AS 名称,xRuns.value('(RunDate)[1]','datetime') AS RunDate,(从@CompetitionIdMapping 中选择竞争ID)从@rrXML.nodes('/RunRecordFile/Competition/Runs') AS E(xRuns)插入运行记录 (数字,经过时间,运行ID)OUTPUT INSERTED.Id INTO @RunRecordIdMapping(RunRecordId)选择xRunRecords.value('(Number)[1]','varchar(10)') AS Number,xRunRecords.value('(ElapsedTime)[1]','numeric(10,5)') AS ElapsedTime,(SELECT RunId FROM @RunIdMapping)从@rrXML.nodes('/RunRecordFile/Competition/Runs/RunRecords') AS E(xRunRecords)INSERT INTO RunRecordSpecialty (差点,团队积分,运行记录ID)选择xRunRecordSpecialty.value('(Handicap)[1]','numeric(10,5)') AS Handicap,xRunRecordSpecialty.value('(TeamPoints)[1]','numeric(10,5)') AS TeamPoints,(SELECT RunRecordId FROM @RunRecordIdMapping)从@rrXML.nodes('/RunRecordFile/Competition/Runs/RunRecordSpecialty') AS E(xRunRecordSpecialty)UPDATE RunRecordFile SET Submitted = GETDATE() WHERE Id = @rrfIdCOMMIT TRAN 比赛结束尝试开始捕捉ROLLBACK TRAN 比赛收尾结尾

解决方案

使用此 SQL，您可以将整个内容放入一个平面声明表中 @tbl:

备注:我将您问题中的 XML 放入名为 @xml 的变量中.根据您的需要调整它...

声明@tbl 表([Competition_Name] [varchar](max) NULL,[Competition_StartDate] [datetime] NULL，[Run_Id] [int] NULL，[Run_Name] [varchar](max) NULL,[Run_RunDate] [日期时间] NULL，[Run_CompetitionId] [int] NULL，[RunRecords_Id] [int] NULL，[RunRecords_Number] [int] NULL，[RunRecords_ElapsedTime] [float] NULL,[RunRecords_RunId] [int] NULL，[RunRecordSpecialty_Id] [int] NULL，[RunRecordSpecialty_Handicap] [int] NULL,[RunRecordSpecialty_TeamPoints] [int] NULL，[RunRecordSpecialty_RunRecordId] [int] NULL);插入@tblSELECT Competition.value('Name[1]','varchar(max)') AS Competition_Name,Competition.value('StartDate[1]','datetime') AS Competition_StartDate,Run.value('Id[1]','int') AS Run_Id,Run.value('Name[1]','varchar(max)') AS Run_Name,Run.value('RunDate[1]','datetime') AS Run_RunDate,Run.value('CompetitionId[1]','int') AS Run_CompetitionId,RunRecords.value('Id[1]','int') AS RunRecords_Id,RunRecords.value('Number[1]','int') AS RunRecords_Number,RunRecords.value('ElapsedTime[1]','float') AS RunRecords_ElapsedTime,RunRecords.value('RunId[1]','int') AS RunRecords_RunId,RunRecordSpecialty.value('Id[1]','int') AS RunRecordSpecialty_Id,RunRecordSpecialty.value('Handicap[1]','int') AS RunRecordSpecialty_Handicap,RunRecordSpecialty.value('TeamPoints[1]','int') AS RunRecordSpecialty_TeamPoints,RunRecordSpecialty.value('RunRecordId[1]','int') AS RunRecordSpecialty_RunRecordIdFROM @xml.nodes('/RunRecordFile/Competition') AS A(Competition)外部应用 Competition.nodes('Runs') AS B(Run)外部应用 Run.nodes('RunRecords') AS C(RunRecords)外部应用 RunRecords.nodes('RunRecordSpecialty') AS D(RunRecordSpecialty);SELECT * FROM @tbl

如果您需要生成的 ID，您只需将列添加到 @tbl 并将它们写在那里，在流程中"或之后使用 UPDATE 语句.>

处理这个平面表应该很容易，只需使用 DISTINCT 选择所需的数据级别并插入行，然后插入下一级等等......

祝你好运！

I'm trying to write a stored procedure to pull information from an XML string and use it to create multiple parent-child relationships. I am trying to push this XML into actual database tables. Basically, the local client will send an XML file to the database and store it as a string. I then need to pull the information out of that string and update the appropriate tables. If this was just a Table-A to Table-B, this wouldn't be so difficult. The problem I'm running into is it need to go from Table-A to Table-B to Table-C to Table-D where applicable. Below is a sample XML:

<RunRecordFile>
    <Competition>
        <Name>Daily</Name>
        <StartDate>11/9/2015 12:40:07 AM</StartDate>
        <Runs>
            <Id>123</Id>
            <Name>Daily Run</Name>
            <RunDate>11/9/2015 12:40:07 AM</RunDate>
            <CompetitionId>1</CompetitionId>
            <RunRecords>
                <Id>001</Id>
                <Number>007</Number>
                <ElapsedTime>23.007</ElapsedTime>
                <RunId>123</RunId>
            </RunRecords>
        </Runs>
        <Runs>
            <Id>456</Id>
            <Name>Daily Run</Name>
            <RunDate>11/9/2015 12:47:07 AM</RunDate>
            <CompetitionId>1</CompetitionId>
            <RunRecords>
                <Id>002</Id>
                <Number>700</Number>
                <ElapsedTime>23.707</ElapsedTime>
                <RunId>456</RunId>
                <RunRecordSpecialty>
                    <Id>1</Id>
                    <Handicap>17</Handicap>
                    <TeamPoints>50000</TeamPoints>
                    <RunRecordId>002</RunRecordId>
                </RunRecordSpecialty>
            </RunRecords>
        </Runs>
    </Competition>
</RunRecordFile>

I've attempted to use a DECLARED table to hold each of the created Primary Keys and to use SQL OUTPUT in order to gather those. When I run my SQL I'm getting (0) Rows Updated. Here's what I've tried in SQL:

CREATE PROC [dbo].[RaceFilePush]
AS
DECLARE @CompetitionIdMapping TABLE ( CompetitionId bigint )
DECLARE @RunIdMapping TABLE ( RunId bigint )
DECLARE @RunRecordIdMapping TABLE ( RunRecordId bigint )
BEGIN
    DECLARE @rrXML AS XML
    DECLARE @rrfId AS BIGINT

    SET @rrfId = (SELECT TOP 1 Id FROM RunRecordFile WHERE Submitted IS NULL)
    SET @rrXML = (SELECT TOP 1 RaceFile FROM RunRecordFile WHERE Id = @rrfId)

    BEGIN TRAN Competitions
    BEGIN TRY

        INSERT INTO Competition (
            Name
            ,StartDate
        )
        OUTPUT INSERTED.Id INTO @CompetitionIdMapping(CompetitionId)
        SELECT
            xCompetition.value('(Name)[1]', 'varchar(225)') AS Name
            ,xCompetition.value('(StartDate)[1]', 'datetime') AS StartDate
            ,@rrfId AS RunRecordFileId
        FROM
            @rrXML.nodes('/RunRecordFile/Competition') AS E(xCompetition)

        INSERT INTO Run (
            Name
            ,RunDate
            ,CompetitionId
        )
        OUTPUT INSERTED.Id INTO @RunIdMapping(RunId)
        SELECT
            xRuns.value('(Name)[1]','varchar(80)') AS Name
            ,xRuns.value('(RunDate)[1]','datetime') AS RunDate
            ,(SELECT CompetitionId FROM @CompetitionIdMapping)
        FROM
            @rrXML.nodes('/RunRecordFile/Competition/Runs') AS E(xRuns)

        INSERT INTO RunRecord (
            Number
            ,ElapsedTime
            ,RunId
        )
        OUTPUT INSERTED.Id INTO @RunRecordIdMapping(RunRecordId)
        SELECT
            xRunRecords.value('(Number)[1]','varchar(10)') AS Number
            ,xRunRecords.value('(ElapsedTime)[1]','numeric(10,5)') AS ElapsedTime
            ,(SELECT RunId FROM @RunIdMapping)
        FROM
            @rrXML.nodes('/RunRecordFile/Competition/Runs/RunRecords') AS E(xRunRecords)

        INSERT INTO RunRecordSpecialty (
            Handicap
            ,TeamPoints
            ,RunRecordId
        )
        SELECT
            xRunRecordSpecialty.value('(Handicap)[1]','numeric(10,5)') AS Handicap
            ,xRunRecordSpecialty.value('(TeamPoints)[1]','numeric(10,5)') AS TeamPoints
            ,(SELECT RunRecordId FROM @RunRecordIdMapping)
        FROM
            @rrXML.nodes('/RunRecordFile/Competition/Runs/RunRecordSpecialty') AS E(xRunRecordSpecialty)

        UPDATE RunRecordFile SET Submitted = GETDATE() WHERE Id = @rrfId

    COMMIT TRAN Competitions
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN Competitions
    END CATCH
END

解决方案

With this SQL you get the whole thing into a flat declared table @tbl:

Remark: I placed the XML from your question into a variable called @xml. Adapt this to your needs...

DECLARE @tbl TABLE (
    [Competition_Name] [varchar](max) NULL,
    [Competition_StartDate] [datetime] NULL,
    [Run_Id] [int] NULL,
    [Run_Name] [varchar](max) NULL,
    [Run_RunDate] [datetime] NULL,
    [Run_CompetitionId] [int] NULL,
    [RunRecords_Id] [int] NULL,
    [RunRecords_Number] [int] NULL,
    [RunRecords_ElapsedTime] [float] NULL,
    [RunRecords_RunId] [int] NULL,
    [RunRecordSpecialty_Id] [int] NULL,
    [RunRecordSpecialty_Handicap] [int] NULL,
    [RunRecordSpecialty_TeamPoints] [int] NULL,
    [RunRecordSpecialty_RunRecordId] [int] NULL
); 

INSERT INTO @tbl
SELECT Competition.value('Name[1]','varchar(max)') AS Competition_Name
      ,Competition.value('StartDate[1]','datetime') AS Competition_StartDate

      ,Run.value('Id[1]','int') AS Run_Id 
      ,Run.value('Name[1]','varchar(max)') AS Run_Name 
      ,Run.value('RunDate[1]','datetime') AS Run_RunDate 
      ,Run.value('CompetitionId[1]','int') AS Run_CompetitionId 

      ,RunRecords.value('Id[1]','int') AS RunRecords_Id 
      ,RunRecords.value('Number[1]','int') AS RunRecords_Number
      ,RunRecords.value('ElapsedTime[1]','float') AS RunRecords_ElapsedTime 
      ,RunRecords.value('RunId[1]','int') AS RunRecords_RunId

      ,RunRecordSpecialty.value('Id[1]','int') AS RunRecordSpecialty_Id 
      ,RunRecordSpecialty.value('Handicap[1]','int') AS RunRecordSpecialty_Handicap 
      ,RunRecordSpecialty.value('TeamPoints[1]','int') AS RunRecordSpecialty_TeamPoints
      ,RunRecordSpecialty.value('RunRecordId[1]','int') AS RunRecordSpecialty_RunRecordId

FROM @xml.nodes('/RunRecordFile/Competition') AS A(Competition)
OUTER APPLY Competition.nodes('Runs') AS B(Run)
OUTER APPLY Run.nodes('RunRecords') AS C(RunRecords)
OUTER APPLY RunRecords.nodes('RunRecordSpecialty') AS D(RunRecordSpecialty)
;
SELECT * FROM @tbl

If you need generated IDs, you just add the columns to @tbl and write them there, either "on the flow" or afterwards wiht UPDATE statement.

It should be easy to work through this flat table, select just the needed data level with DISTINCT and insert the rows, then the next level and so on...

Good luck!

这篇关于多亲子插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆