How to get a self-closing xml tag in Oracle PLSQL?

Question

The Requested output is should be as follows:

<Consignment id="123" date="2017-06-08">
    <Box id="321" />
</Consignment>

where the <box> tag should be self-closing as above.

I am using the following code:

SELECT XMLELEMENT( "Consignment", XMLATTRIBUTES('123' AS "id",sysdate AS "date" ),
            XMLELEMENT( "Box", xmlattributes( '321' as "id" ))     
                 ).getstringval() as xxx FROM DUAL;

but it always return the following results (where the tag <box> has a separated closing tag </box>):

<Consignment id="123" date="2017-06-08">
    <Box id="321"></Box>
</Consignment>

how to get the above <box> tag self-closed?

Answer 1

If you only need to do this with "box", then you can use :

SELECT REPLACE(XMLELEMENT( "Consignment", XMLATTRIBUTES('123' AS "id",sysdate AS "date" ),
            XMLELEMENT( "Box", xmlattributes( '321' as "id" ))     
                 ).getstringval(),'></Box>',' />') as xxx FROM DUAL;

If you have other tags that need to be processed this way, you will need to use REGEXP_REPLACE using the same logic.

Semantically though, both forms represent the exact same data, which is why you can't do what you want to do "easily" with some parameter given to the XML generator (and why maybe you should not do that in the first place!).

Answer 2

If you pass your generated XML fragment/document through the XMLSerialize() function, and specify either INDENT or NO INDENT, then empty tags will be transformed to be self-closed. (Not specifying either leaves them untouched).

SELECT XMLSerialize(CONTENT
    XMLELEMENT( "Consignment", XMLATTRIBUTES('123' AS "id",sysdate AS "date" ),
      XMLELEMENT( "Box", xmlattributes( '321' as "id" ))
  ) as VARCHAR2(4000) INDENT) as xxx FROM DUAL;

XXX                                                                             
--------------------------------------------------------------------------------
<Consignment id="123" date="2017-06-08">
  <Box id="321"/>
</Consignment>

or without formatting:

SELECT XMLSerialize(CONTENT
    XMLELEMENT( "Consignment", XMLATTRIBUTES('123' AS "id",sysdate AS "date" ),
      XMLELEMENT( "Box", xmlattributes( '321' as "id" ))
  ) as VARCHAR2(4000) NO INDENT) as xxx FROM DUAL;

XXX                                                                             
--------------------------------------------------------------------------------
<Consignment id="123" date="2017-06-08"><Box id="321"/></Consignment>

Your question shows the output as indented, but the code you provided does not indent it, so not really sure which of those you actually want.

I've used VARCHAR2 for the data type based on your use of getStringVal, and you can make that smaller if you know the size - or switch to CLOB if you don't, or know it might be too big for VARCHAR2.