Generate XML Data for generic SQL Query

Using below query I've develope a package, which gives XML data if you input you query and tags.

SELECT xmlelement("BusinessGroup1",xmlagg(XMLELEMENT("BusinessGroup2", XMLELEMENT("name", e.full_name),XMLELEMENT ( "Number", e.employee_number)))).getclobval()

FROM per_all_people_f e where trunc(sysdate) between effective_start_date and effective_end_date and rownum <10

Package:

Specification:

create or replace PACKAGE CPAY_GENERATE_XML_FILE
AS

FUNCTION GENERATE_XML_QUERY(
    p_tag       IN VARCHAR2,
    p_query     IN VARCHAR2,
    p_condition IN VARCHAR2)
  RETURN CLOB;

FUNCTION tokens(
    chr_string IN VARCHAR2,
    token      IN VARCHAR2)
  RETURN NUMBER;

FUNCTION tokenize(
    chr_string IN VARCHAR2,
    token      IN VARCHAR2,
    pos        IN NUMBER)
  RETURN VARCHAR2;

END CPAY_GENERATE_XML_FILE;

Package Body:

create or replace
PACKAGE BODY CPAY_GENERATE_XML_FILE
AS
FUNCTION GENERATE_XML_QUERY(
    p_tag       IN VARCHAR2,
    p_query     IN VARCHAR2,
    p_condition IN VARCHAR2)
  RETURN CLOB
AS
  no_tokens        INT;
  no_inside_tokens NUMBER;
  i                INT;
  v_string         VARCHAR2(150);
  v_column         VARCHAR2(150);
  v_tag            VARCHAR2(150);
  v_xml_query      VARCHAR2(4000) := 'SELECT xmlagg(XMLELEMENT("'||p_tag||'"';
  no_tags          NUMBER         :=0;
  v_braces         VARCHAR2(55)   := NULL;
  j                INT;
type r_cursor
IS
  REF
  CURSOR;
    v_xml_cursor r_cursor;
    v_count NUMBER :=0;
    v_xml CLOB;
  BEGIN
    no_tags       := TOKENS( CHR_STRING => p_tag, TOKEN => ' ' );
    IF (no_tags   <> 0) THEN
      v_xml_query := 'select';
      FOR j       IN 1..no_tags
      LOOP
        v_xml_query := v_xml_query||' xmlelement ("'||trim(tokenize(p_tag,' ',j))||'",';
        v_braces    := v_braces || ')';
      END LOOP;
      v_xml_query := v_xml_query||' xmlagg(XMLELEMENT("'||trim(SUBSTR(p_tag,instr(p_tag,' ',-1,1)+1,LENGTH(p_tag)))||'"';
      dbms_output.put_line ('v_xml_query: '||v_xml_query);
    END IF;
    no_tokens := TOKENS( CHR_STRING => p_query, TOKEN => ',' );
    dbms_output.put_line('no_of_tokens:'||no_tokens);
    FOR i IN 1..no_tokens
    LOOP
      v_string :=trim(tokenize(p_query,',',i));
      DBMS_OUTPUT.PUT_LINE('v_string = ' ||v_string);
      v_column := SUBSTR(v_string,0,(instr(v_string,' ',1,1)));
      v_tag    := SUBSTR(v_string,(instr(v_string,' ',1,1)));
      DBMS_OUTPUT.PUT_LINE('v_column = ' ||v_column);
      DBMS_OUTPUT.PUT_LINE('v_tag = ' ||v_tag);
      IF (v_string  IS NOT NULL) THEN
        v_xml_query := v_xml_query|| ', XMLELEMENT('||v_tag||', '||v_column||')';
      END IF;
      v_string := NULL;
      v_column :=NULL;
      v_tag    := NULL;
    END LOOP;
    v_string := trim(SUBSTR(p_query,instr(p_query,',',-1,1)+1,LENGTH(p_query)));
    DBMS_OUTPUT.PUT_LINE('v_string = ' ||v_string);
    v_column := SUBSTR(v_string,0,(instr(v_string,' ',1,1)));
    v_tag    := SUBSTR(v_string,(instr(v_string,' ',1,1)));
    DBMS_OUTPUT.PUT_LINE('v_column = ' ||v_column);
    DBMS_OUTPUT.PUT_LINE('v_tag = ' ||v_tag);
    IF (no_tags   <> 0) THEN
      v_xml_query := v_xml_query|| ', XMLELEMENT('||v_tag||', '||v_column||')))'||v_braces||'.getclobval() '||p_condition;
    ELSE
      v_xml_query := v_xml_query|| ', XMLELEMENT('||v_tag||', '||v_column||'))).getclobval() '||p_condition;
    END IF;
    IF (p_query   IS NULL) THEN
      v_xml_query := 'SELECT xmlagg(XMLELEMENT("'||p_tag||'")) from dual';
    END IF;
    IF (v_xml_query IS NOT NULL) THEN
      EXECUTE immediate (v_xml_query) INTO v_xml;
      RETURN v_xml;
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
    dbms_output.put_line(sqlerrm);
  END GENERATE_XML_QUERY;
FUNCTION tokenize(
    chr_string IN VARCHAR2,
    token      IN VARCHAR2,
    pos        IN NUMBER)
  RETURN VARCHAR2
AS
  v_out   VARCHAR2(100);
  chk_pos NUMBER:=1;
BEGIN
  WHILE instr(chr_string,token,1,chk_pos) > 0
  LOOP
    chk_pos := chk_pos + 1;
  END LOOP;
  IF pos <= chk_pos-1 AND pos > 0 THEN
    --chk_pos := chk_pos - 1;
    IF pos   = 1 THEN
      v_out := SUBSTR(chr_string,instr(chr_string,token,0,1)+1,(instr(chr_string,token,1,1) - instr(chr_string,token,0,1))-1);
    ELSE
      v_out := SUBSTR(chr_string,instr(chr_string,token,1,pos-1)+1,(instr(chr_string,token,1,pos) - instr(chr_string,token,1,pos-1))-1);
    END IF;
  ELSE
    v_out := SUBSTR(chr_string,instr(chr_string,token,-1,1)+1,LENGTH(chr_string));
    --v_out := to_char(length(chr_string));
  END IF;
  IF pos   > chk_pos+1 OR pos <= 0 THEN
    v_out := NULL;
  END IF;
  RETURN v_out;
END tokenize;
FUNCTION tokens(
    chr_string IN VARCHAR2,
    token      IN VARCHAR2)
  RETURN NUMBER
AS
  v_out   VARCHAR2(100);
  chk_pos NUMBER :=1;
  tkn     NUMBER := 0;
BEGIN
  WHILE instr(chr_string,token,1,chk_pos) > 0
  LOOP
    chk_pos := chk_pos + 1;
    tkn     := tkn     + 1 ;
  END LOOP;
  RETURN tkn;
END TOKENS;
END CPAY_GENERATE_XML_FILE;

Example of Procedure call to generate XML:

DECLARE

  P_TAG VARCHAR2(2000);

  P_QUERY VARCHAR2(4000);

  P_CONDITION VARCHAR2(200);

  v_Return clob;

BEGIN

v_Return := '<ORGUSER>';

   P_TAG := 'ORGID';

  P_QUERY := 'ORGANIZATION_ID "organizationid", name "orgname"';

  P_CONDITION := 'from hr_all_organization_units where rownum=1';

  v_Return := v_Return||CPAY_GENERATE_XML_FILE.GENERATE_XML_QUERY(

    P_TAG => P_TAG,

    P_QUERY => P_QUERY,

    P_CONDITION => P_CONDITION

  );

  P_TAG := 'USER1 USER2';

  P_QUERY := 'user_name "uname", user_id "uid"';

  P_CONDITION := 'from fnd_user where rownum<=10';

  v_Return := v_Return||CPAY_GENERATE_XML_FILE.GENERATE_XML_QUERY(

    P_TAG => P_TAG,

    P_QUERY => P_QUERY,

    P_CONDITION => P_CONDITION

  );

 

  v_Return := v_Return||'</ORGUSER>';

  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

END;