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?

`` is identical to ``. Why do you like to change the outline? — Wernfried Domscheit, Jun 09 '17 at 06:57
As Wernfried says, the two format are alternative serializations of an Box element with no text children. Any XML processor should not care which version it received. Typically these questions arise when some downstream process is attempting to manipulate the serialized XML using string manipulation techniques (regex, replace, etc) which is always a bad idea, will typically end in tears, or unnecessary questions on forums such as stack overflow. — mark d drake, Jun 09 '17 at 19:23

score 2 · Answer 1 · answered Jun 08 '17 at 15:48

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!).

score 2 · Answer 2 · answered Jun 08 '17 at 16:08

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.

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

2 Answers2

Linked