遍历

Question

我有三个表table1, table2, table3和col1, col2和identity ID列。这些表关系在数据库中定义。

我正在尝试创建一个存储过程，它接受xml字符串输入并将数据保存到表中。

这是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。

算法：

1. 循环遍历每个节点
2. 向表中插入节点属性
3. 得到最后的同一性值
4. 调用具有最后标识值为外键的子节点
5. 直到不再有子节点为止。

这是处理这种情况的唯一方法吗？如果是这样，如何遍历xml节点？

救命啊！！

谢谢,

埃森

Answer 1

使用合并和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);

硒数据 (选择“纯文本结果”以查看所有结果集)

Answer 2

如果您的代码示例代表所获得的数据类型，并且严格遵循一致的结构，则可以尝试创建一个类来反序列化。下面是一组示例类，这些类将正确地从给定的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内容和应用您的自定义数据库插入逻辑一样简单：

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次左右的往返，并且应该非常快。

Answer 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;