Results 1 to 3 of 3

Thread: Sql/xml

  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: Sql/xml

    i have a table X(
    key number,
    attrname varchar2(50),
    attrval varchar2(5));

    say i have data in the table ((10,"X","XVAL"),(10,"Y","YVAL"))

    and need to extract a fragment
    <data ID=10>
    <X>XVAL</X>
    <Y>YVAL</Y>
    </data>

    How can i achieve this..?
    thanks for the help..
    srini

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    First, you need to get your resulset grouped, so you have only one rowset per each member in DATA.

    From the data you inputted, I see I could do it with:

    Code:
    select * from (
         select x1.key data,
                  nvl((select attrval x from x where attrname = ''X'' and key = x1.key),0) x,
                  nvl((select attrval y from x where attrname = ''Y'' and key = x1.key),0) y
           from x x1
                       )
     group by data, x, y
    (I formatted the null values by each attrval, since XML ignores nulls)

    You must figure out your way around if this data doesn't reflect the actual data you are about to use!

    Now, using DBMS_XMLQUERY package (avaliable on 8i and later), I used:

    Code:
    SQL> var l_clob clob
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    xml CLOB;
      3    ctx dbms_xmlquery.ctxType;
      4  begin
      5    ctx := dbms_xmlquery.newContext('select * from (
      6                                     select x1.key data,
      7                                            nvl((select attrval x from x where attrname = ''X'' and key = x1.key),0) x,
      8                                            nvl((select attrval y from x where attrname = ''Y'' and key = x1.key),0) y
      9                                       from x x1
     10                                     )
     11                                   group by data, x, y');
     12    :l_clob := dbms_xmlquery.getXml(ctx);
     13* end;
    SQL> /
    
    PL/SQL procedure successfully completed.
    
    SQL> print l_clob
    
    L_CLOB
    ----------------------------------------------------------------------------------------------------
    <?xml version = '1.0'?>
    <ROWSET>
       <ROW num="1">
          <DATA>10</DATA>
          <X>XVAL</X>
          <Y>YVAL</Y>
       </ROW>
    </ROWSET>
    
    
    SQL>
    You can try using SETROWTAG and SETROWSETTAG procedures to skip the ROWSET and ROWNUM tags on the resultset.

  3. #3
    Join Date
    Aug 2004
    Posts
    3

    get column value as an XML Element tag.

    Thanks martinez. i tried to solve in a different way;
    a) Created an object called 'X_OBJ' with elements 'X' and 'Y'
    b) I created a function which will take the 'key' as input and return the object, populating the attributes (using case statement).
    c) Then created the XML from dbms_xmlgen package much the same way as you have shown.

    i will try your solution and look at the relative performance..

    thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •