SQL 2012 - 遍历 XML 列表(更好地替代 WHILE 循环) [英] SQL 2012 - iterate through an XML list (better alternative to a WHILE loop)
问题描述
使用 SQL 2012 &将 XML 传递到必须接受该输入的存储过程中对于传递给存储过程的 XML 部分中的每个项目,向表中写入一行.XML 看起来像:
<MyMsg>我的短信</MyMsg><MsgTime>2013-09-25 10:52:37.098</MsgTime><发送到列表><SendTo>John</SendTo><SendTo>James</SendTo><SendTo>Rob</SendTo><SendTo>Pete</SendTo><SendTo>山姆</SendTo></SendToList></MyXML>
存储过程的输出应该是插入到表中的 5 行(上面每个 SendTo
一个),并且每行在 MyMsg
和 MsgTime
该表中的字段.
我可以得到 SendTo
的数量,也可以得到 XML SendToList
,但我不知道如何遍历它来进行插入.
我可以使用以下 SQL 来获取 XML 中的内容.
SELECTx.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)
目前,我正在使用变量和 WHILE
来遍历 SendToList 中的项目,但我知道必须有更好的方法.
SELECT @msgTo= @XML_In.value('(/MyXML/SendToList/SendTo[position()=sql:variable("@cnt")])[1]','VARCHAR(100)')
以上为我获取了 SendToList 中每个项目的值.
如果我选择变量@mlst,我可以看到我需要循环遍历的 XML 的结构.
<SendTo>John</SendTo><SendTo>James</SendTo><SendTo>Rob</SendTo><SendTo>Pete</SendTo><SendTo>山姆</SendTo></SendToList>
即使 WHILE
有效,它也会一个接一个地插入.我在想可用的方法应该能够做到这一切而不是循环,但我对使用它们来做我需要做的事情知之甚少.
希望得到任何帮助或建议.
如果您需要做一些需要循环的事情(例如,您想向每个收件人发送电子邮件,则可以使用游标:
>
声明cur游标本地fast_forward for选择s.c.value('(text())[1]', 'nvarchar(max)') 作为 SendTo,m.c.value('(MyMsg/text())[1]', 'nvarchar(max)') 作为 MyMsg,m.c.value('(MsgTime/text())[1]', 'nvarchar(max)') 作为 MsgTime来自@XML_In.nodes('MyXML') as m(c)外部应用 m.c.nodes('SendToList/SendTo') 作为 s(c)开路而 1 = 1开始获取 cur 到 @SendTo、@MyMsg、@MsgTime如果@@fetch_status <>0 休息--========================================-- 在这里做你需要做的--========================================结尾关闭当前解除分配cur
如果您只想将行插入到某个表中,您可以通过一个简单的插入来完成:
插入<您的表格>(发送至、我的消息、消息时间)选择s.c.value('(text())[1]', 'nvarchar(max)') 作为 SendTo,m.c.value('(MyMsg/text())[1]', 'nvarchar(max)') 作为 MyMsg,m.c.value('(MsgTime/text())[1]', 'nvarchar(max)') 作为 MsgTime来自@XML_In.nodes('MyXML') as m(c)外部应用 m.c.nodes('SendToList/SendTo') 作为 s(c)
Using SQL 2012 & getting XML passed into a stored procedure that must take that input & write a row to the table for each of the items that is in a section of the XML that is passed to the stored procedure. The XML looks like:
<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>
The output of the stored procedure should be 5 rows inserted into a table (one for each SendTo
above), and each having the same value in the MyMsg
and MsgTime
fields in that table.
I can get the count of the number of SendTo
and can get the XML SendToList
but I don't know how to iterate through it to do the inserts.
I can use the following SQL to get what's in the 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)
Currently, I'm using variables and a WHILE
to loop through the items in the SendToList, but I know there's got to be a better way.
SELECT @msgTo= @XML_In.value('(/MyXML/SendToList/SendTo[position()=sql:variable("@cnt")])[1]','VARCHAR(100)')
The above gets me the value of each item in the SendToList.
If I select the variable @mlst, I can see the structure of the XML that I need to loop through.
<SendToList>
<SendTo>John</SendTo>
<SendTo>James</SendTo>
<SendTo>Rob</SendTo>
<SendTo>Pete</SendTo>
<SendTo>Sam</SendTo>
</SendToList>
Even though the WHILE
works, it's doing one insert right after the other. I was thinking that the methods available should be able to do it all instead of looping but I don't know enough about using them to do what I need to do.
Would appreciate any help or suggestions.
If you need to do to something that requires a loop (for example, you want to send email to each recipient, than you can use a cursor:
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
If you just want to insert rows into some table, you can do this in one simple insert:
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 2012 - 遍历 XML 列表(更好地替代 WHILE 循环)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!