遍历XML SQL INSERT [英] iterate through XML sql insert

问题描述

我有三个表表1，表2，表3 与 COL1，COL2 和身份 ID 列。这些表都关系在数据库中定义

I have three tables table1, table2, table3 with col1, col2 and identity ID column. These table relationship are defined in the database.

我想创建一个接受XML字符串输入一个存储过程，并保存数据到表中。

I am trying to create a stored procedure that accepts xml string input and save that data into tables.

这是XML输入

<root>
 <table1 col1='a' col2='b'>
  <table2Array>
   <table2 col1='c' col2='d'>
    <table3array>
     <table3 col1='g' col2='h' />
     <table3 col1='i' col2='j' />
    </table3array>
   </table2>
  <table2 col1='c' col2='d'>
   <table3array>
    <table3 col1='k' col2='l' />
    <table3 col1='i' col2='j' />
   </table3array>
  </table2>
 </table2Array>
</table1>
 <table1 col1='a' col2='b'>
  <table2Array>
   <table2 col1='e' col2='f'>
    <table3array>
     <table3 col1='i' col2='j' />
     <table3 col1='i' col2='j' />
    </table3array>
   </table2>
   <table2 col1='e' col2='f'>
    <table3array>
     <table3 col1='g' col2='h' />
     <table3 col1='g' col2='h' />
    </table3array>
   </table2>
  </table2Array>
 </table1>
</root>

此XML是来自第三方的对象来了，我们就不用管了修改第三方对象发出不同格式的XML。

This xml is coming from a third party object and we don't have control to modify the third party object to emit a different format xml.

算法：

1. 在通过每个节点循环
2. 插入节点属性到表
3. 在获取最后一个标识值
4. 在通话的子节点与最后一个标识值作为外键
5. 请直到没有更多的子节点

这是处理这种情况的唯一途径？如果是的话如何通过XML节点迭代？

Is this the only way to handle this situation? If so how to iterate through xml node?

请帮助！

谢谢

也先

推荐答案

使用合并和输出，你可以做到这一点，而不使用描述的技术<一个循环href="http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id">here.

Using merge and output you can do this without a loop using a technique described here.

我假设你的表结构是这样的。

I assume your table structure is like this.

create table Table1
(
  Table1ID int identity primary key,
  Col1 char(1),
  Col2 char(1)
)

create table Table2
(
  Table2ID int identity primary key,
  Table1ID int references Table1(Table1ID),
  Col1 char(1),
  Col2 char(1)
)

create table Table3
(
  Table3ID int identity primary key,
  Table2ID int references Table2(Table2ID),
  Col1 char(1),
  Col2 char(1)
)

在code

The code

declare @T1 table (XMLCol xml, TargetID int);
declare @T2 table (XMLCol xml, TargetID int);

merge Table1 as T
using (select T1.XMLCol.query('*'),
              T1.XMLCol.value('@col1', 'char(1)'),
              T1.XMLCol.value('@col2', 'char(1)')
       from @XML.nodes('/root/table1') as T1(XMLCol)) as S(XMLCol, Col1, Col2)
on 1 = 0
when not matched then
  insert (Col1, Col2) values (S.Col1, S.Col2)
output S.XMLCol, inserted.Table1ID into @T1;          

merge Table2 as T
using (select T2.XMLCol.query('*'),
              T1.TargetID,
              T2.XMLCol.value('@col1', 'char(1)'),
              T2.XMLCol.value('@col2', 'char(1)')
       from @T1 as T1  
         cross apply T1.XMLCol.nodes('table2Array/table2') as T2(XMLCol)) as S(XMLCol, ID1, Col1, Col2)
on 1 = 0
when not matched then
  insert (Table1ID, Col1, Col2) values (S.ID1, S.Col1, S.Col2)
output S.XMLCol, inserted.Table2ID into @T2;          

insert into Table3(Table2ID, Col1, Col2)
select T2.TargetID,
       T3.XMLCol.value('@col1', 'char(1)'),
       T3.XMLCol.value('@col2', 'char(2)') 
from @T2 as T2
  cross apply T2.XMLCol.nodes('table3array/table3') as T3(XMLCol);

SE-数据（选择纯文本结果可以看到所有的结果集）

SE-Data (select "Text-only results" to see all resultsets)

这篇关于遍历XML SQL INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！