Creating XML with PL/SQL

Question

i need to create XML with PL/SQL and have two problems in my solution.

Sample code looks like this:

select xmlelement( 
    "tag_one",
    XMLAGG(
        xmlelement("user", 
            xmlforest( 
                'something' "two"
                , 'else' "tree"
                , NULL "four" 
                , ( 
                    select sys_xmlagg( 
                        xmlelement( 
                            "secondGoup", 
                            xmlforest(
                                'true' "five"
                                , 123 "six"
                            ) 
                        ) 
                    ) 
                    FROM dual
                ) "group_tag" 
            )
        )
    ) 
) as "XML_QUERY" 
FROM dual

XML i get looks like this

<tag_one>
    <user>
        <two>something</two>
        <tree>else</tree>
        <group_tag>
            <ROWSET>
                <secondGoup>
                    <five>true</five>
                    <six>123</six>
                </secondGoup>
            </ROWSET>
        </group_tag>
    </user>
</tag_one>

And problems with it are:

1.) I don't need ROWSET tag in it and don't know how to remove it

2.) I need empty tag four in my XML

<four></four>

I sholud mention that there can be multiple secondGoup tags, but this code can generate them without problem

Noting just in case you have some better way of creating this XML

<tag_one>
    <user>
        <two>something</two>
        <tree>else</tree>
        <four></four>
        <group_tag>
            <secondGoup>
                <five>true</five>
                <six>123</six>
            </secondGoup>
            <secondGoup>
                <five>true</five>
                <six>456</six>
            </secondGoup>
            <secondGoup>
                <five>false</five>
                <six>789</six>
            </secondGoup>
        </group_tag>
    </user>
</tag_one>

Query for five and six looks like this

SELECT t1.field1 "five"
, t2.field2 "six"
FROM table1 t1
JOIN table2 t2 ON t1.field4 = t2.field2
WHERE t2.field3 = 3

And this returns X number of rows (sometimes it returns 1, sometimes 2, 3, 20,...). Added 3 in WHERE just as example, there is variable value in real query

hmmftg · Accepted Answer · 2017-01-15 13:59:59Z

Hi do you mean to get this output:

<tag_one>
    <user>
        <two>something</two>
        <tree>else</tree>
        <four></four>
        <group_tag>
            <secondGoup>
                <five>true</five>
                <six>123</six>
            </secondGoup>
            <secondGoup>
                <five>true</five>
                <six>456</six>
            </secondGoup>
            <secondGoup>
                <five>true</five>
                <six>789</six>
            </secondGoup>
        </group_tag>
    </user>
</tag_one>

this is the correct query:

SELECT XMLELEMENT (
          "tag_one",
          XMLAGG (
             XMLELEMENT (
                "user",
                XMLFOREST (
                   'something' "two",
                   'else' "tree",
                   '' "four",
                   XMLFOREST (
                      XMLFOREST ('true' "five", 123 "six") "secondGoup",
                      XMLFOREST ('true' "five", 456 "six") "secondGoup",
                      XMLFOREST ('true' "five", 789 "six") "secondGoup") "group_tag"))))
          AS "XML_QUERY"
  FROM DUAL

===========EDIT=================

Hi Again, for you dynamic query we no longer can use XMLELEMT and related functions because they shall return clob and it will be a mess to work with them, I came to a very long pl/sql block for you here(it's way too larger and complicated, but I make it usable for your case):

DECLARE
   L_XMLTYPE             XMLTYPE;
   L_DOMDOC              DBMS_XMLDOM.DOMDOCUMENT;
   L_ROOT_NODE           DBMS_XMLDOM.DOMNODE;

   L_SUPP_NUM_ELEMENT    DBMS_XMLDOM.DOMELEMENT;
   L_SUPP_NAME_ELEMENT   DBMS_XMLDOM.DOMELEMENT;

   L_SUPP_NUM_NODE       DBMS_XMLDOM.DOMNODE;
   L_SUPP_NAME_NODE      DBMS_XMLDOM.DOMNODE;

   L_SUPP_NUM_TNODE      DBMS_XMLDOM.DOMNODE;
   L_SUPP_NAME_TNODE     DBMS_XMLDOM.DOMNODE;

   L_SUPP_NUM_TEXT       DBMS_XMLDOM.DOMTEXT;
   L_SUPP_NAME_TEXT      DBMS_XMLDOM.DOMTEXT;

   L_C1                  DBMS_XMLDOM.DOMELEMENT;
   L_C1_NODE             DBMS_XMLDOM.DOMNODE;

   L_C2                  DBMS_XMLDOM.DOMELEMENT;
   L_C2_NODE             DBMS_XMLDOM.DOMNODE;

   L_C2_TEXT             DBMS_XMLDOM.DOMTEXT;
   L_C2_TEXT_NODE        DBMS_XMLDOM.DOMNODE;

   L_SUPPLIER_ELEMENT    DBMS_XMLDOM.DOMELEMENT;
   L_SUPPLIER_NODE       DBMS_XMLDOM.DOMNODE;
   L_SUP_NODE            DBMS_XMLDOM.DOMNODE;
BEGIN
   -- Create an empty XML document
   L_DOMDOC := DBMS_XMLDOM.NEWDOMDOCUMENT;

   -- Create a root node
   L_ROOT_NODE := DBMS_XMLDOM.MAKENODE (L_DOMDOC);

   -- Create a new Supplier Node and add it to the root node
   L_SUP_NODE :=
      DBMS_XMLDOM.APPENDCHILD (
         L_ROOT_NODE,
         DBMS_XMLDOM.MAKENODE (
            DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'tag_one')));


   L_C1 := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'user');
   L_C1_NODE :=
      DBMS_XMLDOM.APPENDCHILD (L_SUP_NODE, DBMS_XMLDOM.MAKENODE (L_C1));

   L_C2 := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'two');
   L_C2_NODE :=
      DBMS_XMLDOM.APPENDCHILD (L_C1_NODE, DBMS_XMLDOM.MAKENODE (L_C2));

   L_C2_TEXT := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, 'something');
   L_C2_TEXT_NODE :=
      DBMS_XMLDOM.APPENDCHILD (L_C2_NODE, DBMS_XMLDOM.MAKENODE (L_C2_TEXT));

   L_C2 := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'three');
   L_C2_NODE :=
      DBMS_XMLDOM.APPENDCHILD (L_C1_NODE, DBMS_XMLDOM.MAKENODE (L_C2));

   L_C2_TEXT := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, 'else');
   L_C2_TEXT_NODE :=
      DBMS_XMLDOM.APPENDCHILD (L_C2_NODE, DBMS_XMLDOM.MAKENODE (L_C2_TEXT));

   L_C2 := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'group_tag');
   L_C2_NODE :=
      DBMS_XMLDOM.APPENDCHILD (L_C1_NODE, DBMS_XMLDOM.MAKENODE (L_C2));

   FOR SUP_REC IN (SELECT T1.FIELD1, T2.FIELD2
                     FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.FIELD4 = T2.FIELD2
                    WHERE T2.FIELD3 = 3)
   LOOP
      -- For each record, create a new Supplier element
      -- and add this new Supplier element to the Supplier Parent node
      L_SUPPLIER_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'secondGoup');
      L_SUPPLIER_NODE :=
         DBMS_XMLDOM.APPENDCHILD (
                                  L_C2_NODE,
                                  DBMS_XMLDOM.MAKENODE (L_SUPPLIER_ELEMENT)
                                 );

      -- Each Supplier node will get a Number node which contains the Supplier Number as text
      L_SUPP_NUM_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'five');
      L_SUPP_NUM_NODE :=
         DBMS_XMLDOM.APPENDCHILD (
                                  L_SUPPLIER_NODE,
                                  DBMS_XMLDOM.MAKENODE (L_SUPP_NUM_ELEMENT)
                                 );
      L_SUPP_NUM_TEXT := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, SUP_REC.FIELD1);
      L_SUPP_NUM_TNODE :=
         DBMS_XMLDOM.APPENDCHILD (
                                  L_SUPP_NUM_NODE,
                                  DBMS_XMLDOM.MAKENODE (L_SUPP_NUM_TEXT)
                                 );

      -- Each Supplier node will get a Name node which contains the Supplier Name as text
      L_SUPP_NAME_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'six');
      L_SUPP_NAME_NODE :=
         DBMS_XMLDOM.APPENDCHILD (
                                  L_SUPPLIER_NODE,
                                  DBMS_XMLDOM.MAKENODE (L_SUPP_NAME_ELEMENT)
                                 );
      L_SUPP_NAME_TEXT :=
         DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, SUP_REC.FIELD2);
      L_SUPP_NAME_TNODE :=
         DBMS_XMLDOM.APPENDCHILD (
                                  L_SUPP_NAME_NODE,
                                  DBMS_XMLDOM.MAKENODE (L_SUPP_NAME_TEXT)
                                 );
   END LOOP;

   L_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE (L_DOMDOC);
   DBMS_XMLDOM.FREEDOCUMENT (L_DOMDOC);

   DBMS_OUTPUT.PUT_LINE (L_XMLTYPE.GETCLOBVAL);
END;

Output:

<tag_one>
  <user>
    <two>something</two>
    <three>else</three>
    <group_tag>
      <secondGoup>
        <five>123</five>
        <six>014</six>
      </secondGoup>
      <secondGoup>
        <five>456</five>
        <six>011</six>
      </secondGoup>
      <secondGoup>
        <five>789</five>
        <six>011</six>
      </secondGoup>
      <secondGoup>
        <five>012</five>
        <six>011</six>
      </secondGoup>
      <secondGoup>
        <five>345</five>
        <six>011</six>
      </secondGoup>
      <secondGoup>
        <five>678</five>
        <six>011</six>
      </secondGoup>
      <secondGoup>
        <five>901</five>
        <six>011</six>
      </secondGoup>
      <secondGoup>
        <five>234</five>
        <six>011</six>
      </secondGoup>
      <secondGoup>
        <five>567</five>
        <six>011</six>
      </secondGoup>
    </group_tag>
  </user>
</tag_one>

Please note that you should not pass NULL to create a empty tag, you can use NVL() function to convert possible NULLs to whatever you want.
beautiful, no rowset tag, love it :) just one thing, values of five and six should be extracted with separate querry (and they have different number of rows), not shure how can i do that here. Added example in original post
@BeRightBack Can you add a sample query of your five and six values?
I have used code from this link: quest4apps.com/create-xml-using-dbms_xmldom
GREAT!!! Just wrote using dbms_xmldom, works like charm, thank you. BTW, it's not complicated at all, it's simple as it gets, just looks big but that is not a problem

Collectives™ on Stack Overflow

Creating XML with PL/SQL

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related