SQL 2012 - iterate through an XML list (better alternative to a WHILE loop)
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)
steve_o
Updated on June 11, 2022Comments
-
steve_o about 2 years
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 theMyMsg
andMsgTime
fields in that table.I can get the count of the number of
SendTo
and can get the XMLSendToList
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.
-
steve_o over 10 yearsI would be inserting straight into a table - your second example worked great. Thank you!
-
SearchForKnowledge over 9 yearsWhat if I want to read the data and save it to a variable to be displayed in the front end? stackoverflow.com/questions/26426412/…
-
user3885927 almost 8 yearsThe first option required me to declare SendTo, MyMsg, MsgTime