Home » SQL & PL/SQL » SQL & PL/SQL » XML into one row (12.1.0.2)
XML into one row [message #683159] Wed, 09 December 2020 03:36 Go to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
HEllo

I would like to get xml structure into one row.

I generate xml from table like:

 select xmlserialize(
       document xmlelement("DOCUMENT"
     , xmlattributes( ...
And XML structure looks like:

<DOCUMENT>
   <HEADER>
        <LANGUAGE>CZ</LANGUAGE>
        <OUTPUTTYPE>EMAIL</OUTPUTTYPE>
        <CURRENCY>CZK</CURRENCY>
        <CUSTOMERINFO>
            <WP_ID>M0112301</WP_ID>
              <CONTACTADDRESS>
                <ADDRESSLINE>name </ADDRESSLINE>
                <ADDRESSLINE> street </ADDRESSLINE>
                <ADDRESSLINE>town</ADDRESSLINE>
                <COUNTRY>CZ</COUNTRY>
            </CONTACTADDRESS>
            <CUSTOMERTYPE>
                <SEGMENT>C</SEGMENT>
            </CUSTOMERTYPE>
        </CUSTOMERINFO>
    </HEADER>
</DOCUMENT>
But I need to get all records into the one row. Like this:

<DOCUMENT></DOCUMENT> <HEADER><LANGUAGE>CZ</LANGUAGE><OUTPUTTYPE>EMAIL</OUTPUTTYPE><CURRENCY>CZK</CURRENCY>...

Does anybody know how to make it right ??

Thanks

Martin
Re: XML into one row [message #683161 is a reply to message #683159] Wed, 09 December 2020 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A test case would be welcome.

Re: XML into one row [message #683162 is a reply to message #683161] Wed, 09 December 2020 05:09 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
What you mean test case?
Just convert xml structure into the one row
Re: XML into one row [message #683163 is a reply to message #683162] Wed, 09 December 2020 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What you mean test case?

Did you just click on the link? I doubt, maybe before posting a question you could read what we provide.
Also this has been asked to you many times, for instance:
Michel Cadot wrote on Wed, 11 November 2020 14:28

Michel Cadot wrote on Wed, 26 August 2020 12:26

This is clear as mud.
Provide a test case with its result.

As this a continuation of this topic, you should post this question in this one.
The query I gave you returns ONE row, I displayed it in several lines to have a pretty display.
Note that your question asked for an output on several lines.

Just remove xmlserialize. Maybe before using something you should read the documentation about it.

We are still waiting for your feedback in your previous topic.

Re: XML into one row [message #683164 is a reply to message #683163] Wed, 09 December 2020 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select xmlserialize(
  2           document
  3             xmlelement("EMPLOYEES",
  4                        xmlagg(xmlelement("EMPLOYEE",
  5                                          xmlforest(empno, ename))))
  6           indent size=2) res
  7  from emp
  8  where rownum <= 3
  9  /
RES
--------------------------------------------------------------------------------
<EMPLOYEES>
  <EMPLOYEE>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
  </EMPLOYEE>
  <EMPLOYEE>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
  </EMPLOYEE>
  <EMPLOYEE>
    <EMPNO>7521</EMPNO>
    <ENAME>WARD</ENAME>
  </EMPLOYEE>
</EMPLOYEES>

1 row selected.

SQL> select
  2             xmlelement("EMPLOYEES",
  3                        xmlagg(xmlelement("EMPLOYEE",
  4                                          xmlforest(empno, ename))))
  5           res
  6  from emp
  7  where rownum <= 3
  8  /
RES
--------------------------------------------------------------------------------
<EMPLOYEES><EMPLOYEE><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME></EMPLOYEE><EMPLOYEE
><EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME></EMPLOYEE><EMPLOYEE><EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME></EMPLOYEE></EMPLOYEES>

1 row selected.
Re: XML into one row [message #683166 is a reply to message #683164] Wed, 09 December 2020 06:34 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Thanks for help
This is exactly what I need

Re: XML into one row [message #683167 is a reply to message #683162] Wed, 09 December 2020 06:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
mape wrote on Wed, 09 December 2020 06:09
What you mean test case?
Just convert xml structure into the one row
It is one row:

SQL> select rownum,xmlserialize(document dbms_xmlgen.getxmltype('select * from dept')) from dual;

    ROWNUM XMLSERIALIZE(DOCUMENTDBMS_XMLGEN.GETXMLTYPE('SELECT*FROMDEPT'))
---------- ---------------------------------------------------------------
         1 <ROWSET>
            <ROW>
             <DEPTNO>10</DEPTNO>
             <DNAME>ACCOUNTING</DNAME>
             <LOC>NEW YORK</LOC>
            </ROW>
            <ROW>
             <DEPTNO>20</DEPTNO>
             <DNAME>RESEARCH</DNAME>
             <LOC>DALLAS</LOC>
            </ROW>
            <ROW>
             <DEPTNO>30</DEPTNO>
             <DNAME>SALES</DNAME>
             <LOC>CHICAGO</LOC>
            </ROW>
            <ROW>
             <DEPTNO>40</DEPTNO>
             <DNAME>OPERATIONS</DNAME>
             <LOC>BOSTON</LOC>
            </ROW>
           </ROWSET>


SQL>
You probably meant one line. Use NO INDENT:

SQL> select rownum,xmlserialize(document dbms_xmlgen.getxmltype('select * from dept') no indent) from dual;

    ROWNUM XMLSERIALIZE(DOCUMENTDBMS_XMLGEN.GETXMLTYPE('SELECT*FROMDEPT')NOINDENT)
---------- ------------------------------------------------------------------------------------------------------------------------
         1 <ROWSET><ROW><DEPTNO>10</DEPTNO><DNAME>ACCOUNTING</DNAME><LOC>NEW YORK</LOC></ROW><ROW><DEPTNO>20</DEPTNO><DNAME>RESEARC
           H</DNAME><LOC>DALLAS</LOC></ROW><ROW><DEPTNO>30</DEPTNO><DNAME>SALES</DNAME><LOC>CHICAGO</LOC></ROW><ROW><DEPTNO>40</DEP
           TNO><DNAME>OPERATIONS</DNAME><LOC>BOSTON</LOC></ROW></ROWSET>


SQL>
SY.
Re: XML into one row [message #683168 is a reply to message #683167] Wed, 09 December 2020 07:06 Go to previous message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
with NO INDENT is a nice solution

thanks
Previous Topic: ten-digit hexadecimal number
Next Topic: Display Numeric Value from Text Field
Goto Forum:
  


Current Time: Thu Mar 28 12:19:16 CDT 2024