XML into one row [message #683159] |
Wed, 09 December 2020 03:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #683167 is a reply to message #683162] |
Wed, 09 December 2020 06:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
mape wrote on Wed, 09 December 2020 06:09What 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.
|
|
|
|