Call stored proc using xml output from a table
Posted
by
user263097
on Stack Overflow
See other posts from Stack Overflow
or by user263097
Published on 2011-11-21T17:47:18Z
Indexed on
2011/11/21
17:50 UTC
Read the original article
Hit count: 152
Under a tight deadline and I know I can figure this out eventually but I don't have much time to do it on my own.
I have a table that has columns for customer id and account number among many other additional columns. There could be many accounts for a single customer (Many rows with the same customer id but different account number).
For each customer in the table I need to call a stored procedure and pass data from my table as xml in the following format. Notice that the xml is for all of the customers accounts.
<Accounts>
<Account>
<AccountNumber>12345</AccountNumber>
<AccountStatus>Open</AccountStatus>
</Account>
<Account>
<AccountNumber>54321</AccountNumber>
<AccountStatus>Closed</AccountStatus>
</Account>
</Accounts>
So I guess I need help with 2 things. First, how to get the data in this xml format. I assuming I'll use some variation of FOR XML. The other thing is how do I group by customer id and then call a sproc for each customer id?
© Stack Overflow or respective owner
