Call stored proc using xml output from a table
- by user263097
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?
