none
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.
    Thursday, June 12, 2008 11:59 AM

Answers

All replies