Wednesday, September 28, 2011

Example of DBMS_XMLGEN.getxml to generate XML Tag using oracle query

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 (301464)
    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      = 301464
  ) AS HOLDS_DETAIL
FROM DUAL'
)

FROM DUAL

2 comments:

Bushra Saad said...

something is wrong .... can't run this script. :(

Raju Ch said...

Enter the Order Number and Run the script

Post a Comment

Best Blogger TipsGet Flower Effect