Home » Developer & Programmer » JDeveloper, Java & XML » Problem in generating a XML document (Oracle 11g)
Problem in generating a XML document [message #533048] Sun, 27 November 2011 21:16 Go to next message
suedemby
Messages: 9
Registered: October 2011
Location: USA
Junior Member
I need to generate a document that include fields provided in following.
CREATE TABLE t
    (x_customer_po                  VARCHAR2(20 BYTE),
    x_accessory_code               VARCHAR2(3 BYTE),
    x_install_loc                  CHAR(3 BYTE));


INSERT INTO apps.T 
VALUES('N45882','D0C','MFG');
INSERT INTO apps.T 
VALUES('N45882','K0B','MFG');
INSERT INTO apps.T 
VALUES('N45882','LK2','MFG');
INSERT INTO apps.T 
VALUES('N45882','R0B','MFG');
INSERT INTO apps.T 
VALUES('N45883','B0A','MFG');
INSERT INTO apps.T 
VALUES('N45883','M0E','MFG');
INSERT INTO apps.T 
VALUES('N45883','R0B','MFG');
INSERT INTO apps.T 
VALUES('N45884','D0A','MFG');
INSERT INTO apps.T 
VALUES('N45884','K0B','MFG');
INSERT INTO apps.T 
VALUES('N45884','LK2','MFG');
INSERT INTO apps.T 
VALUES('N45884','Q0F','MFG');
INSERT INTO apps.T 
VALUES('N45885','D00','MFG');
INSERT INTO apps.T 
VALUES('N45885','H8B','MFG');
INSERT INTO apps.T 
VALUES('N45886','D0A','MFG');
INSERT INTO apps.T 
VALUES('N45886','D0B','MFG');
INSERT INTO apps.T 
VALUES('N45886','OYF','MFG');
INSERT INTO apps.T 
VALUES('N45887',NULL,'MFG');
INSERT INTO apps.T 
VALUES('N45888',NULL,'MFG');
COMMIT;


select XMLSerialize
    ( DOCUMENT(  
 SELECT XMLAGG ( 
       XMLELEMENT ( "OrderAcknowledgments",
           XMLELEMENT ("Sender", 
                  XMLATTRIBUTES (decode (fnd_global.CONC_REQUEST_ID,-1,999999999) AS "ID",
                                 'Cars' AS "Name"  )
                       ), --Sender      
           XMLELEMENT ( "DateTime",REPLACE (to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF1'),' ','T') ),
           XMLELEMENT ("Acknowledgments",
                (SELECT xmlagg( 
                        XMLELEMENT ("Acknowledgment",
                            XMLELEMENT ( "Vehicle",
                                XMLELEMENT ("CustomerPONum",   x_customer_po) ,                            
                                XMLELEMENT ("Options",
                                            XMLAGG (
                                                XMLELEMENT ("Option",                                                    
                                                        XMLForest ( x_accessory_code AS "Code", 
                                                                    x_install_loc AS  "InstallLocation")
                                                   
                                                            ) --"Option"
                                                        )
                                             -- WHERE t.X_CUSTOMER_PO = x_customer_po 
                                            --GROUP BY x_customer_po
                                             --xmlag
                                           ) --options
                                       )--vehicles
                                       
                                     ) --ack
                                 )--xmlagg
                     FROM t
                     GROUP BY x_customer_po
                     ) ----select
                       ) --acks
               ) --orderacks
           )--xmlagg
    FROM dual ) --select
     )--document
FROM dual;


How can I get the output as formated XML?
--output:
<OrderAcknowledgments><Sender ID="999999999" Name="Cars"></Sender><DateTime>2011-11-27T22:00:14.3</DateTime><Acknowledgments><Acknowledgment><Vehicle><CustomerPONum>N45882 </CustomerPONum><Options><Option><Code>D0C</Code><InstallLocation>MFG</InstallLocation></Option><Option><Code>R0B</Code> <InstallLocation>MFG</InstallLocation></Option><Option><Code>LK2</Code><InstallLocation>MFG</InstallLocation></Option><Option ><Code>K0B</Code><InstallLocation>MFG</InstallLocation></Option></Options></Vehicle></Acknowledgment><Acknowledgment><Vehicle ><CustomerPONum>N45883</CustomerPONum><Options><Option><Code>B0A</Code><InstallLocation>MFG</InstallLocation></Option><Option ><Code>R0B</Code><InstallLocation>MFG</InstallLocation></Option><Option><Code>M0E</Code><InstallLocation>MFG</InstallLocation ></Option></Options></Vehicle></Acknowledgment><Acknowledgment><Vehicle><CustomerPONum>N45884</CustomerPONum><Options><Option ><Code>D0A</Code><InstallLocation>MFG</InstallLocation></Option><Option><Code>Q0F</Code><InstallLocation>MFG</InstallLocation ></Option><Option><Code>LK2</Code><InstallLocation>MFG</InstallLocation></Option><Option><Code>K0B</Code><InstallLocation >MFG</InstallLocation></Option></Options></Vehicle></Acknowledgment><Acknowledgment><Vehicle><CustomerPONum>N45885</CustomerPONum ><Options><Option><Code>D00</Code><InstallLocation>MFG</InstallLocation></Option><Option><Code>H8B</Code><InstallLocation >MFG</InstallLocation></Option></Options></Vehicle></Acknowledgment><Acknowledgment><Vehicle><CustomerPONum>N45886</CustomerPONum ><Options><Option><Code>D0A</Code><InstallLocation>MFG</InstallLocation></Option><Option><Code>OYF</Code><InstallLocation >MFG</InstallLocation></Option><Option><Code>D0B</Code><InstallLocation>MFG</InstallLocation></Option></Options></Vehicle ></Acknowledgment><Acknowledgment><Vehicle><CustomerPONum>N45887</CustomerPONum><Options><Option><InstallLocation>MFG</InstallLocation ></Option></Options></Vehicle></Acknowledgment><Acknowledgment><Vehicle><CustomerPONum>N45888</CustomerPONum><Options><Option ><InstallLocation>MFG</InstallLocation></Option></Options></Vehicle></Acknowledgment></Acknowledgments></OrderAcknowledgments >
Re: Problem in generating a XML document [message #533049 is a reply to message #533048] Sun, 27 November 2011 21:30 Go to previous messageGo to next message
suedemby
Messages: 9
Registered: October 2011
Location: USA
Junior Member
I found the answer, using AS CLOB INDENT.
Re: Problem in generating a XML document [message #533062 is a reply to message #533049] Mon, 28 November 2011 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 66053
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, I often asked myself how to get it.
Where do you put this option?

Regards
Michel
Re: Problem in generating a XML document [message #533161 is a reply to message #533062] Mon, 28 November 2011 09:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
Michel,

INDENT is an option in the XMLSERIALIZE syntax. If you are not using XMLSERIALIZE then EXTRACT('*') is supposed to provide "pretty printing", but it seems to have the opposite effect on my system. It might work the other way around for you. Perhaps there is some default parameter setting that I have not been able to find or perhaps it is dependent upon version and/or operating system and the usage of EXTRACT('*') acts like a toggle. Please see the demonstrations below

-- INDENT with XMLSERIALIZE:
-- default no indent:
SCOTT@orcl_11gR2> select xmlserialize
  2  	      (document xmltype
  3  		 ('<a><b><c>something</c></b></a>'))
  4  from   dual
  5  /

XMLSERIALIZE(DOCUMENTXMLTYPE('<A><B><C>SOMETHING</C></B></A>'))
--------------------------------------------------------------------------------
<a><b><c>something</c></b></a>

1 row selected.

-- indent with default size of 2 spaces:
SCOTT@orcl_11gR2> select xmlserialize
  2  	      (document xmltype
  3  		 ('<a><b><c>something</c></b></a>')
  4  	       indent)
  5  from   dual
  6  /

XMLSERIALIZE(DOCUMENTXMLTYPE('<A><B><C>SOMETHING</C></B></A>')INDENT)
--------------------------------------------------------------------------------
<a>
  <b>
    <c>something</c>
  </b>
</a>


1 row selected.

-- indent 5 spaces:
SCOTT@orcl_11gR2> select xmlserialize
  2  	      (document xmltype
  3  		 ('<a><b><c>something</c></b></a>')
  4  	       indent size = 5)
  5  from   dual
  6  /

XMLSERIALIZE(DOCUMENTXMLTYPE('<A><B><C>SOMETHING</C></B></A>')INDENTSIZE=5)
--------------------------------------------------------------------------------
<a>
     <b>
          <c>something</c>
     </b>
</a>


1 row selected.


-- EXTRACT('*') for "pretty printing" works the opposite from what documentation says on my system:
-- should not be indented, but is:
SCOTT@orcl_11gR2> select xmltype ('<a><b><c>something</c></b></a>')
  2  from   dual
  3  /

XMLTYPE('<A><B><C>SOMETHING</C></B></A>')
--------------------------------------------------------------------------------
<a>
  <b>
    <c>something</c>
  </b>
</a>


1 row selected.

-- should produce indentation but does the opposite:
SCOTT@orcl_11gR2> select xmltype ('<a><b><c>something</c></b></a>').extract('*')
  2  from   dual
  3  /

XMLTYPE('<A><B><C>SOMETHING</C></B></A>').EXTRACT('*')
--------------------------------------------------------------------------------
<a><b><c>something</c></b></a>

1 row selected.


Re: Problem in generating a XML document [message #533173 is a reply to message #533161] Mon, 28 November 2011 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 66053
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I now know why I didn't find it, I mostly work on 10.2 and this option does not exist in this version. Wink
In 11.2, on Windows XP, I got the same output than you on the first 3 queries, on the latest 2 ones here's what I get (11.2.0.1.0):
SQL> select xmltype ('<a><b><c>something</c></b></a>')
  2  from   dual
  3  /
XMLTYPE('<A><B><C>SOMETHING</C></B></A>')
-------------------------------------------------------------------------
<a><b><c>something</c></b></a>

1 row selected.

SQL> select xmltype ('<a><b><c>something</c></b></a>').extract('*')
  2  from   dual
  3  /
XMLTYPE('<A><B><C>SOMETHING</C></B></A>').EXTRACT('*')
-------------------------------------------------------------------------
<a><b><c>something</c></b></a>

1 row selected.

So it seems I have to use xmlserialize to indent which is a reasonable thing to add. Smile
Tomorrow I will check on other systems (AIX, Linux).

Thanks and regards
Michel
Re: Problem in generating a XML document [message #533278 is a reply to message #533173] Tue, 29 November 2011 03:24 Go to previous message
Michel Cadot
Messages: 66053
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On Linux (11.2.0.1 and 11.2.0.2), I get the same thing than you.
On AIX (11.2.0.2), I get the same thing than what I got on Windows XP.

Regards
Michel
Previous Topic: Problem creating a JAR File in JDeveloper, deploy
Next Topic: Using InputStream and OutputStream updating BLOB in database
Goto Forum:
  


Current Time: Mon Dec 17 08:44:45 CST 2018