Thursday, July 7, 2011

XML tags directly out of the SQL query with Example

SELECT DBMS_XMLGEN.getxml(
'SELECT CURSOR(SELECT oha.order_number,
                     ola.ordered_item,
                     ola.ordered_quantity
                FROM ont.oe_order_headers_all oha,
                     ont.oe_order_lines_all ola
               WHERE oha.header_id = ola.header_id
                 and oha.order_number in (&order_number) order by ola.line_id) as order_detail,
       CURSOR(SELECT ohd.name, ohs.hold_comment
                FROM ont.oe_hold_sources_all ohs,
                     ont.oe_order_holds_all ohld,
                     ont.oe_hold_definitions ohd,
                     ont.oe_order_headers_all oha,
                     ont.oe_order_lines_all ola
               WHERE oha.header_id = ola.header_id
                 AND ola.line_id = ohld.line_id
                 and ohld.hold_release_id is null
                 AND ohld.hold_source_id = ohs.hold_source_id
                 AND ohs.hold_id =  ohd.hold_id
                 AND oha.order_number = &&order_number) as holds_detail
FROM DUAL')
FROM DUAL

No comments:

Post a Comment