1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sys_xmlagg

Discussion in 'SQL PL/SQL' started by Phatz, Jan 31, 2012.

  1. Phatz

    Phatz Guest

    Hi, Not sure if this is in the right place as am noob to this forum.

    Have started looking into XML based querying on Oracle. Am on 11G

    I am trying to get data out of a table which contains XML but held in text type field.

    Using the following query works but doesnt work. The table it queries has 3 rows of data in xml format but held in varchar2 type. I am trying to concat these rows together to make one xml doc and insert in to XMLTYPE column in another table but the query creates an element for each row of data as opposed to one element for all the data enclosed for example in the output it shows 3 X <TEXT_VALUE></TEXT_VALUE> elements where I only want <ROWSET> and everything between. PLease see attached could not post directly as treated as spam

    Content form file.

    SELECT 1,SYS_XMLAGG(SYS_XMLGEN(text_value)) XMLAGG
    from al_langxmltext
    where object_key = 435 and object_type = 13
    order by seqnum,xmlagg
     

    Attached Files:

  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India