OraFAQ Forum: SQL & PL/SQL » XML into one row

Home » SQL & PL/SQL » SQL & PL/SQL » XML into one row (12.1.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XML into one row [message #683159]

Wed, 09 December 2020 03:36

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

Report message to a moderator

Re: XML into one row [message #683161 is a reply to message #683159]

Wed, 09 December 2020 04:24

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

A test case would be welcome.

Report message to a moderator

Re: XML into one row [message #683162 is a reply to message #683161]

Wed, 09 December 2020 05:09

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

What you mean test case?
Just convert xml structure into the one row

Report message to a moderator

Re: XML into one row [message #683163 is a reply to message #683162]

Wed, 09 December 2020 05:41

Michel Cadot
Messages: 68418
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.

Report message to a moderator

Re: XML into one row [message #683164 is a reply to message #683163]

Wed, 09 December 2020 05:48

Michel Cadot
Messages: 68418
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.

Report message to a moderator

Re: XML into one row [message #683166 is a reply to message #683164]

Wed, 09 December 2020 06:34

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Thanks for help
This is exactly what I need

Report message to a moderator

Re: XML into one row [message #683167 is a reply to message #683162]

Wed, 09 December 2020 06:40

Solomon Yakobson
Messages: 3212
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.

Report message to a moderator

Re: XML into one row [message #683168 is a reply to message #683167]

Wed, 09 December 2020 07:06

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

with NO INDENT is a nice solution

thanks

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 28 03:42:44 CDT 2023