在創建具有關係的 ID 時導入 XML 數據

我需要將 XML 從文件解析為表，同時通過自定義生成的 ID 值保留它們的關係。

例如，如果我有以下 XML：

<root>
  <construction>
     <constructionName>randomname1</constructionName>
     <project>
        <projectname>another randomname</projectname>
        <businesspartners>
           <partnername>bilbo bagginses</partnername>
        </businesspartners>
        <employees>
           <employee>
              <empname>frodo</empname>
              <empaddress>etc...</empaddress>
           </employee>
        </employees>
     </project>
     <info>
        <randElement></randElement>
     </info>
     <constructionType>houses</constructiontype>
  </construction>
  <construction>
     <...(etc, same as above, times n^10)/>
  </construction>
</root>

從那，我需要為下表生成數據：

CONSTRUCTION (CONSTRUCTION_ID INT PRIMARY KEY
  , CONSTRUCTIONNAME VARCHAR..
  , CONSTRUCTIONTYPE VARCHAR.. )

PROJECT (PROJECT_ID INT PRIMARY KEY
  , CONSTRUCTION_ID INT FOREIGN KEY REFERENCES CONSTRUCTION
  , PROJECTNAME VARCHAR.., )


BUSINESSPARTNERS (BUSINESSPARTNERS_ID INT PRIMARY KEY
  , PROJECT_ID INT FOREIGN KEY REFERENCES PROJECT
  , PARTNERNAME VARCHAR..)

etc...

基本上，這個想法是構造具有完整引用的表來表示 XML。表結構已存在，無法更改以適應此腳本。這只是一個簡單的問題，即能夠進行相同類型的 XML 解析，然後將數據添加到表中，同時生成正確的引用 ID 值，就像我們之前在集成中所做的那樣。僅使用 SQL Server 專門執行此操作，而不使用 SSIS。

現在，實際情況和有問題的文件相當龐大，所以我絕不期待一個完整的答案。只是關於從哪裡開始尋找的提示。我對處理 XML 非常缺乏經驗。

目前，我首選的解決方案是使用 導入數據OPENROWSET，然後使用動態 SQLOPENXML將文件解析到表格中，基本上一次循環一個元素及其子元素。但這似乎比其他一些更聰明的方法更麻煩。

ID 值是如何生成的？

這是問題的一部分。目前，他們不是。這個想法是每個CONSTRUCTION元素將被分配一個ID從 1 開始增加的值。然後所有的子元素都CONSTRUCTION將引用ID分配給父元素的相同元素，依此類推。基本上，它只是將 XML 中的數據分成多個表，同時保持引用完整性不變。

使用標識列作為主鍵我不會有任何問題，只要關係不會因此而混淆。我不知道該怎麼做，所以我假設必須以某種方式手動定義創建 ID 的邏輯，而不是身份？

您可以使用使用 merge..output 中描述的技術的變體來獲取 source.id 和 target.id 之間的映射，以及 Adam Machanic 在Dr. OUTPUT 中或：我如何學會停止擔心並愛上 MERGE。

您merge在表變數中使用和擷取生成的 ID 以及屬於該 ID 的 XML 片段，然後在將行添加到子表時使用該表變數。

declare @C table
(
 CONSTRUCTION_ID int primary key,
 PROJECT xml
);

merge CONSTRUCTION as T
using (
     select T.X.value('(constructionName/text())[1]', 'varchar(30)') as CONSTRUCTIONNAME,
            T.X.value('(constructionType/text())[1]', 'varchar(30)') as CONSTRUCTIONTYPE,
            T.X.query('project') as PROJECT
     from @xml.nodes('/root/construction') as T(X)
     ) as S
on 0 = 1
when not matched by target then
 insert (CONSTRUCTIONNAME, CONSTRUCTIONTYPE) 
 values (S.CONSTRUCTIONNAME, S.CONSTRUCTIONTYPE)
output inserted.CONSTRUCTION_ID, S.PROJECT into @C;

declare @P table
(
 PROJECT_ID int primary key,
 BUSINESSPARTNERS XML
);

merge PROJECT as T
using (
     select C.CONSTRUCTION_ID,
            T.X.value('(projectname/text())[1]', 'varchar(30)') as PROJECTNAME,
            T.X.query('businesspartners') as BUSINESSPARTNERS
     from @C as C
       cross apply C.PROJECT.nodes('/project') as T(X)
     ) as S
on 0 = 1
when not matched by target then
 insert (CONSTRUCTION_ID, PROJECTNAME)
 values(S.CONSTRUCTION_ID, S.PROJECTNAME)
output inserted.PROJECT_ID, S.BUSINESSPARTNERS into @P;

insert into BUSINESSPARTNERS(PROJECT_ID, PARTNERNAME)
select P.PROJECT_ID,
      T.X.value('text()[1]', 'varchar(30)')
from @P as P
 cross apply P.BUSINESSPARTNERS.nodes('/businesspartners/partnername') as T(X);

SQL小提琴

引用自：https://dba.stackexchange.com/questions/54710