循环访问 XML SQL 插入

使用合并和output您可以使用此处描述的技术在没有循环的情况下执行此操作。

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

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)
)

代码

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 数据（选择"纯文本结果"以查看所有结果集）

如果你的代码示例代表了你获得的数据类型，并且严格遵守一致的结构，你可以尝试创建一个类来反序列化。 下面是一组类的示例，这些类将从给定的 XML 示例中正确反序列化：

[XmlRoot("root")]
public class MyCustomStructure
{
    [XmlElement("table1")]
    public Table1Structure[] Table1Array { get; set; }
}
[XmlRoot("table1")]
public class Table1Structure
{
    [XmlAttribute("col1")]
    public string Col1 { get; set; }
    [XmlAttribute("col2")]
    public string Col2 { get; set; }
    [XmlArray("table2Array")]
    [XmlArrayItem("table2")]
    public Table2Structure[] Table2Array { get; set; }
}
[XmlRoot("table2")]
public class Table2Structure
{
    [XmlAttribute("col1")]
    public string Col1 { get; set; }
    [XmlAttribute("col2")]
    public string Col2 { get; set; }
    [XmlArray("table3array")]
    [XmlArrayItem("table3")]
    public Table3Structure[] Table3Array { get; set; }
}
public class Table3Structure
{
    [XmlAttribute("col1")]
    public string Col1 { get; set; }
    [XmlAttribute("col2")]
    public string Col2 { get; set; }
}

将应用反序列化的示例代码：

var ser = new XmlSerializer(typeof(MyCustomStructure));
// if xml is in a string, use the following:
var sr = new StringReader(xml);
var xr = new XmlTextReader(sr);
// if xml is in a stream, use the following:
var xr = new XmlTextReader(stream);
// if xml is in an XmlElement, use the following:
var xr = new XmlNodeReader(element);
// result contains an instance of MyCustomStructure
var result = ser.Deserialize(xr);

从这里开始，它就像循环遍历每个循环的 MyCustomStructure with 的内容并应用自定义数据库插入逻辑一样简单：

for each (var table1 in result.Table1Array)
{
    // insert table1, get inserted ID
    for each (var table2 in table1.Table2Array)
    {
        // insert table2, use table1 inserted ID, get table2 ID
        for each (var table3 in table2.Table3Array)
        {
            // insert table3, use table2 inserted ID
        }
    }
}

如果您担心要插入的数据规模的性能，可以尝试将数据作为表值参数传递，或者作为可以在 SQL 端轻松解析的其他格式传递。 您还可以批量上传所有 table1 条目，取回所有 ID，然后批量上传具有正确映射 ID 的所有 table2 条目，取回所有新 ID 等，这总共需要 3 次左右的往返，并且应该非常快。

认为这对寻找类似解决方案的人会有所帮助。这就是我在SQL中处理遍历节点的方式

        declare @xmlRoot as xml 
        set @xmlRoot= '<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>'
        Declare @col1 varchar(100),@col2 varchar(100), @table1Counter int, @table2Counter int
        select @table1Counter=0
        DECLARE table1_cursor CURSOR FOR 
            SELECT
            col1 =  item.value('./@col1', 'varchar(100)'), 
            col2 =  item.value('./@col2', 'varchar(100)') 
            FROM @xmlRoot.nodes('root/table1') AS T(item);
            OPEN table1_cursor
            FETCH NEXT FROM table1_cursor 
            INTO  @col1 ,@col2
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    --insert into table1 and get id into a variable
                     set @table1Counter=@table1Counter+1

                    DECLARE table2_cursor CURSOR FOR 
                    SELECT
                    col1 =  item.value('./@col1', 'varchar(100)'),
                    col2 = item.value('./@col2', 'varchar(100)') 
                    FROM @xmlRoot.nodes('root/table1[sql:variable("@table1Counter")]/table2Array/table2') AS T(item);                            
                        OPEN table2_cursor
                        FETCH NEXT FROM table2_cursor INTO @col1 ,@col2                       
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                --insert into table2 and get id into a varialbe
                                set @table2Counter = @table2Counter+1
                                 --do same for table3 similar to table2
                                FETCH NEXT FROM table2_cursor INTO @col1 ,@col2
                             END
                        CLOSE table2_cursor
                        DEALLOCATE table2_cursor                            
                    FETCH NEXT FROM table1_cursor 
                    INTO  @col1, @col2                      
                END 
        CLOSE table1_cursor;
        DEALLOCATE table1_cursor;