主页 / dba / 问题 / 54710
Accepted
Kahn
Asked: 2013-12-10 06:35:17 +0800 CST

在创建具有关系的 ID 时导入 XML 数据

  • 772

我需要将 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 的逻辑，而不是身份？

sql-server import

1 个回答

  1. Best Answer

    您可以使用使用 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小提琴

    • 5

相关问题