Craeting a loop for XML-parameter

Question

Hi!

I'd need to create a loop in sqlserver2005 which goes through a xml-input.

Here's what I have so far:

create procedure sp_test_xml
    @xml_input xml
as
  -- start the loop here
    select @xml_input.value('(/root/CustomerDescription/@CustID)[2]','int') --this selects only the second row
 -- end loop
GO


-- calling the procedure
sp_test_xml '<root>
<CustomerDescription CustID="101" CustomerName="ABCL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
<CustomerDescription CustID="102" CustomerName="HAL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
</root>';

-------------------
I've been trying to find the answer but just can not find any info about this loop-structure.
I quess I could use cursor but don't know the syntax for xml-parameter.

Any help would be appreciated.

Answer 1

I have presented how to do this in this article : XML Workshop XVII - Writing a LOOP to process XML elements in TSQL

Answer 2

Thank you. This was very helpful.