SQL 2012-遍历XML列表（比WHILE循环更好的替代方法）

Question

使用SQL 2012并将XML传递到存储过程中，该过程必须接受该输入，并为传递到该存储过程的XML部分中的每个项目在表中写一行。XML看起来像：

<MyXML>
    <MyMsg>My Text Message</MyMsg>
    <MsgTime>2013-09-25 10:52:37.098</MsgTime>
    <SendToList>
    <SendTo>John</SendTo>
    <SendTo>James</SendTo>
    <SendTo>Rob</SendTo>
    <SendTo>Pete</SendTo>
    <SendTo>Sam</SendTo>
    </SendToList>
</MyXML>

存储过程的输出应该是将5行插入到一个表中（SendTo上面每个插入一行），并且在该表的MyMsg和MsgTime字段中每个字段具有相同的值。

我可以得到的数量SendTo并可以获取XML，SendToList但是我不知道如何遍历它来进行插入。

我可以使用以下SQL来获取XML中的内容。

SELECT 
x.value('(/MyXML/MyMsg)[1]', 'VARCHAR(1024)'),
x.value('(/MyXML/MsgTime)[1]', 'DATETIME'),
   @max = x.query('<e> { count(/MyXML/SendToList/SendTo) } </e>').value('e[1]','int'),
   @mlst = x.query('/MyXML/SendTo')
    FROM @XML_In.nodes('//MyXML') i(x)

目前，我正在使用变量和aWHILE来遍历SendToList中的项目，但是我知道必须有一种更好的方法。

SELECT @msgTo= @XML_In.value('(/MyXML/SendToList/SendTo[position()=sql:variable("@cnt")])[1]','VARCHAR(100)')

上面的代码让我获得了SendToList中每个项目的值。
如果选择变量@mlst，则可以看到需要循环通过的XML的结构。

<SendToList>
   <SendTo>John</SendTo>
   <SendTo>James</SendTo>
   <SendTo>Rob</SendTo>
   <SendTo>Pete</SendTo>
   <SendTo>Sam</SendTo>
</SendToList>

即使WHILE工作正常，它也会在一个插入之后接一个插入。我当时以为可用的方法应该能够完成所有操作，而不是循环执行，但是我对使用它们来做我需要做的事还不了解。

将不胜感激任何帮助或建议。

Answer 1

如果您需要执行需要循环的操作（例如，要向每个收件人发送电子邮件，则可以使用游标：

declare cur cursor local fast_forward for
    select
        s.c.value('(text())[1]', 'nvarchar(max)') as SendTo,
        m.c.value('(MyMsg/text())[1]', 'nvarchar(max)') as MyMsg,
        m.c.value('(MsgTime/text())[1]', 'nvarchar(max)') as MsgTime
    from @XML_In.nodes('MyXML') as m(c)
        outer apply m.c.nodes('SendToList/SendTo') as s(c)

open cur
while 1 = 1
begin
    fetch cur into @SendTo, @MyMsg, @MsgTime
    if @@fetch_status <> 0 break

    --=======================================
    -- do what you need here 
    --=======================================
end
close cur
deallocate cur

如果只想将行插入到某个表中，则可以通过一个简单的插入操作来完成：

insert into <Your table>
(
    SendTo, MyMsg, MsgTime
)
select
    s.c.value('(text())[1]', 'nvarchar(max)') as SendTo,
    m.c.value('(MyMsg/text())[1]', 'nvarchar(max)') as MyMsg,
    m.c.value('(MsgTime/text())[1]', 'nvarchar(max)') as MsgTime
from @XML_In.nodes('MyXML') as m(c)
    outer apply m.c.nodes('SendToList/SendTo') as s(c)

[sql fiddle demo](http://sqlfiddle.com/#!3/d41d8/21491)