Database and XML

[kensington45]

I have one table with 10 fields and 100 records in Oracle 9i. I want to put the information in XML to be fetched later on another network into another Oracle 9i database.

Please advise if this is possible where I will get the 100 records with the 10 fields and transfer it over into XML?
I assume I can do it using Server side languages such as ColdFusion or Java?

 

[mikeaj1]

Oracle has built in ways to do this. You can use SQLX, in which you change your query to return the xml structure you want, or you can also use Oracle's XDK.

SQLX example:

SELECT XMLELEMENT("PARTS",
          XMLAGG(
             XMLELEMENT("PART",
                XMLFOREST(NVL(unit_price, -999) "LIST_PRICE",
                          NVL(adjusted_unit_price, -999) "UNIT_PRICE")
             )
          )
       ).getClobVal()
  INTO v_return
  FROM qp_preq_lines_tmp
 ORDER BY line_index;

XDK example:
SELECT DBMS_XMLGEN.getxml('SELECT NVL(unit_price, -999) LIST_PRICE, NVL(adjusted_unit_price, -999) UNIT_PRICE
										 FROM qp_preq_lines_tmp
										ORDER BY line_index')
  INTO v_return
  FROM dual;

You can google for this, and find more documentation on these libraries. Hope this helps!