Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: Oracle to XML using Java / SAX

    I have to get data from an Oracle table as an XML file.
    The table has 5 fields in it.

    The output XML file has to be in a specific format which has 10 elements in it out of which 5 of them map to the table fields.
    It is not a problem to have those remaining 5 elements empty w/o data in the XML.

    Could anyone suggest me a way to use Java or SAX or any other means in which I can create an XML output in the format that I have?

    It would be very helpful if someone has a sample code too.


    -AI

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Yesterday you posted the same question & nobody responded.
    Today you posted it again. What's changed?
    AFAIK, no mind reading software exists today.
    What you need is software the automagically "knows" to create FIVE (not 4 & not 6; but 5) additional "empty" columns; which also would require unique names
    Sorry, but I suspect that you'll have actually produce the code to meet your requirements yourself; since the requirements are rather unique.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    On 9 onwards, DBMS_XMLGEN.GetXml appears to do what you require...

    Code:
    create table fred (a number(1), b varchar2(10), c date);
    insert into fred (a,b,c) values (1,'One',sysdate);
    insert into fred (a,b,c) values (2,'Two',sysdate+1);
    insert into fred (a,b,c) values (3,'Three',sysdate+2);
    
    select dbms_xmlgen.getXml( 'select fred.*, 0 as d, 0 as e from fred', 0 ) from dual;
    
    <?xml version="1.0"?>
    <ROWSET>
     <ROW>
      <A>1</A>
      <B>One</B>
      <C>09-NOV-04</C>
      <D>0</D>
      <E>0</E>
     </ROW>
     <ROW>
      <A>2</A>
      <B>Two</B>
      <C>10-NOV-04</C>
      <D>0</D>
      <E>0</E>
     </ROW>
     <ROW>
      <A>3</A>
      <B>Three</B>
      <C>11-NOV-04</C>
      <D>0</D>
      <E>0</E>
     </ROW>
    </ROWSET>
    I don't think it likes to export tags for null fields though - maybe you can get around that with a proper schema definition or by just exporting zero's as I've done above.

    I note that DBMS_XMLQUERY has a procedure "useNullAttributeIndicator" which I guess might force an export of null fields. You'd need to read up on this. I've never used the XML packages before.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Feb 2004
    Posts
    19

    Thumbs up

    Thanks Bill.
    This would help me.

    -AI
    --------------------------------------------------------------------------
    Most folks can lead others to knowledge, but some do keep wondering about mind-reading s/w !

Posting Permissions

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