Post reply

Retrieving data from xml data type randomly using stored procedure

  • May 18, 2009 at 4:46 am

    #200558

    Hi All,

    I want to write a stored procedure in which the values from the xml data type is taken randomly one at a time, so that i want to insert huge amount of data in different table.

    Retrieving values from xml randomly for insertion into anoother table.

    For example if the xml file is as

    BangaloreHyderabad etc..., i want to read the value from this xml data column randomly using stored procedure

    Thanking you,

    Yours faithfully,

    Syed Tameemuddin

  • May 18, 2009 at 10:57 am

    #996507

    You can sort by NEWID()...

    DECLARE @xml XML

    SELECT @xml = '

    1

    2

    3

    4

    5

    6

    7

    '

    SELECT TOP(3)

    T.C.value('(text())[1]', 'int')

    FROM @xml.nodes('root/item') T(C)

    ORDER BY NEWID()

    Greets

    Flo

  • May 18, 2009 at 11:25 pm

    #996733

    Thanks for the reply,

    You didnt get my question right i want to select the values from that xml data column randomly, because I wanted to Insert a lot of data with different combinations in a new table.

    if in the xml cities are like bangalore, hyderabad,pune, madurai, chennai, secunderabad

    then

    if table consists of two columns. then output should be like

    ID City

    1 secunderabad

    2 bangalore

    3 chennai

    4 pune

    5 hyderabad

    .

    .

    .

    .

    and so on, if there are 1000 cities, till all 1000 cities are inserted. It should select the city name randomly not in any order. Cities insertion can be in any order selected randomly

    Thanks

    Syed

  • May 19, 2009 at 12:43 am

    #996741

    Thanks for the reply it is working great.

    Thanks again

    Syed.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply