Home » SQL & PL/SQL » SQL & PL/SQL » issue inserting multiple lines into one row (toad/oracle, 11.0.0.116, windows 7 x64)
icon8.gif  issue inserting multiple lines into one row [message #621332] Tue, 12 August 2014 11:21 Go to next message
martinez77
Messages: 4
Registered: August 2014
Junior Member
Hello all,

I am simply trying to insert multiple rows into on row. One insert value is hard coded the rest are pulled from a table.

So am i trying to insert some values into one row and have been unable to unearth any solution. I would appreciate any help.

So all I want to do is insert the following code into one row of a table that i have created. As of right now it creates 8 rows instead of 1.

Code:
insert into cdy_list (list_code)
select * from (
select '<GLOBALLIST name=' || '"' || 'Products' || '">'
from products
where rownum = 1

union all select
'<LISTITEM value=' || '"' || name || '"/>'
from products
where visible = 'Y'

union ALL select
'</GLOBALLIST>'
FROM PRODUCTS
WHERE ROWNUM = 1) ;

thanks in advance,

Martinez
Re: issue inserting multiple lines into one row [message #621333 is a reply to message #621332] Tue, 12 August 2014 11:25 Go to previous messageGo to next message
BlackSwan
Messages: 23026
Registered: January 2009
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: issue inserting multiple lines into one row [message #621334 is a reply to message #621332] Tue, 12 August 2014 11:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2513
Registered: May 2013
Location: World Wide on the Web
Senior Member
What do you mean by inserting multiple rows into one row? I would appreciate if you post a test case.
Re: issue inserting multiple lines into one row [message #621336 is a reply to message #621332] Tue, 12 August 2014 11:38 Go to previous messageGo to next message
joy_division
Messages: 4544
Registered: February 2005
Location: East Coast USA
Senior Member
Do you mean just to append the data from three different SELECTs into a single value?
Re: issue inserting multiple lines into one row [message #621340 is a reply to message #621334] Tue, 12 August 2014 12:02 Go to previous messageGo to next message
martinez77
Messages: 4
Registered: August 2014
Junior Member
Sure let me give a better background.

my goal: Insert the queried data into one row of a table named cdy_list and the column name is (list_code)

When I run the code this is what is returned from Toad output window


first query creates:

'<GLOBALLISTNAME='||'"'||'PRODUCTS'||'">'
----------------------------------------------------------------------
<GLOBALLIST name="Products">

last query creates:

<LISTITEM value="eLKE - Accruit"/>
<LISTITEM value="eLKE"/>
<LISTITEM value="PACS"/>
<LISTITEM value="TDS"/>
<LISTITEM value="Tracker"/>
<LISTITEM value="Fleet"/>
</GLOBALLIST>

8 rows selected.


what is want is row 1 and column 1 to have the following

<GLOBALLIST name="Products">
<LISTITEM value="eLKE - Accruit"/>
<LISTITEM value="eLKE"/>
<LISTITEM value="PACS"/>
<LISTITEM value="TDS"/>
<LISTITEM value="Tracker"/>
<LISTITEM value="Fleet"/>
</GLOBALLIST>


Re: issue inserting multiple lines into one row [message #621342 is a reply to message #621332] Tue, 12 August 2014 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59739
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle 11.0.0.116 does not exist.

insert into cdy_list (list_code)
select listagg(val) within group (order by rn)
from (
select 1 rn, '<GLOBALLIST name=' || '"' || 'Products' || '">' val
from products
where rownum = 1
union all select 2, '<LISTITEM value=' || '"' || name || '"/>'
from products
where visible = 'Y'
union ALL select 3, '</GLOBALLIST>'
FROM PRODUCTS
WHERE ROWNUM = 1)
/
Re: issue inserting multiple lines into one row [message #621343 is a reply to message #621336] Tue, 12 August 2014 12:04 Go to previous messageGo to next message
martinez77
Messages: 4
Registered: August 2014
Junior Member
If that gets me my results then sure I'll append.
I honestly haven't had enough exposure to be sure.
#Noob
Re: issue inserting multiple lines into one row [message #621345 is a reply to message #621342] Tue, 12 August 2014 12:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
Registered: January 2010
Senior Member
This looks like XML generation and therefore should be using XML functions:

select  xmlserialize(
                     document
                     xmlelement("GLOBALLIST",
                                xmlattributes('Products' as "product"),
                                xmlagg(
                                       xmlelement(
                                                  "LISTITEM",
                                                  xmlattributes(name as "value")
                                                 )
                                      )
                               )
                     indent size = 2
                     ) xml
  from  products
/


For example:

SQL> set long 10000
SQL> select  xmlserialize(
  2                       document
  3                       xmlelement("GLOBALLIST",
  4                                  xmlattributes('Products' as "product"),
  5                                  xmlagg(
  6                                         xmlelement(
  7                                                    "LISTITEM",
  8                                                    xmlattributes(prod_name as "value")
  9                                                   )
 10                                        )
 11                                 )
 12                       indent size = 2
 13                       ) xml
 14    from  sh.products
 15  /

XML
--------------------------------------------------------------------------------
<GLOBALLIST product="Products">
  <LISTITEM value="5MP Telephoto Digital Camera"/>
  <LISTITEM value="17&quot; LCD w/built-in HDTV Tuner"/>
  <LISTITEM value="Envoy 256MB - 40GB"/>
  <LISTITEM value="Y Box"/>
  <LISTITEM value="Mini DV Camcorder with 3.5&quot; Swivel LCD"/>
  <LISTITEM value="Envoy Ambassador"/>
  <LISTITEM value="Laptop carrying case"/>
  <LISTITEM value="Home Theatre Package with DVD-Audio/Video Play"/>
  <LISTITEM value="18&quot; Flat Panel Graphics Monitor"/>
  <LISTITEM value="Envoy External Keyboard"/>
  <LISTITEM value="External 101-key keyboard"/>
  <LISTITEM value="PCMCIA modem/fax 28800 baud"/>
  <LISTITEM value="SIMM- 8MB PCMCIAII card"/>
  <LISTITEM value="SIMM- 16MB PCMCIAII card"/>
  <LISTITEM value="Multimedia speakers- 3&quot; cones"/>
  <LISTITEM value="Unix/Windows 1-user pack"/>
  <LISTITEM value="8.3 Minitower Speaker"/>
  <LISTITEM value="Mouse Pad"/>
  <LISTITEM value="1.44MB External 3.5&quot; Diskette"/>
  <LISTITEM value="Multimedia speakers- 5&quot; cones"/>
  <LISTITEM value="PCMCIA modem/fax 19200 baud"/>
  <LISTITEM value="External 6X CD-ROM"/>
  <LISTITEM value="External 8X CD-ROM"/>
  <LISTITEM value="Envoy External 6X CD-ROM"/>
  <LISTITEM value="Envoy External 8X CD-ROM"/>
  <LISTITEM value="Internal 6X CD-ROM"/>
  <LISTITEM value="Internal 8X CD-ROM"/>
  <LISTITEM value="O/S Documentation Set - English"/>
  <LISTITEM value="O/S Documentation Set - German"/>
  <LISTITEM value="O/S Documentation Set - French"/>
  <LISTITEM value="O/S Documentation Set - Spanish"/>
  <LISTITEM value="O/S Documentation Set - Italian"/>
  <LISTITEM value="O/S Documentation Set - Kanji"/>
  <LISTITEM value="Standard Mouse"/>
  <LISTITEM value="Deluxe Mouse"/>
  <LISTITEM value="Keyboard Wrist Rest"/>
  <LISTITEM value="CD-R Mini Discs"/>
  <LISTITEM value="Music CD-R"/>
  <LISTITEM value="CD-RW, High Speed, Pack of 10"/>
  <LISTITEM value="CD-RW, High Speed Pack of 5"/>
  <LISTITEM value="CD-R, Professional Grade, Pack of 10"/>
  <LISTITEM value="OraMusic CD-R, Pack of 10"/>
  <LISTITEM value="CD-R with Jewel Cases, pACK OF 12"/>
  <LISTITEM value="DVD-R Disc with Jewel Case, 4.7 GB"/>
  <LISTITEM value="DVD-RAM Jewel Case, Double-Sided, 9.4G"/>
  <LISTITEM value="DVD-R Discs, 4.7GB, Pack of 5"/>
  <LISTITEM value="DVD-R Discs, 4.7GB, Pack of 5"/>
  <LISTITEM value="DVD-RW Discs, 4.7GB, Pack of 3"/>
  <LISTITEM value="3 1/2&quot; Bulk diskettes, Box of 50"/>
  <LISTITEM value="3 1/2&quot; Bulk diskettes, Box of 100"/>
  <LISTITEM value="Model CD13272 Tricolor Ink Cartridge"/>
  <LISTITEM value="Model SM26273 Black Ink Cartridge"/>
  <LISTITEM value="Model NM500X High Yield Toner Cartridge"/>
  <LISTITEM value="Model A3827H Black Image Cartridge"/>
  <LISTITEM value="Model K3822L Cordless Phone Battery"/>
  <LISTITEM value="Model C9827B Cordless Phone Battery"/>
  <LISTITEM value="Model K8822S Cordless Phone Battery"/>
  <LISTITEM value="Model C93822D Wireless Phone Battery"/>
  <LISTITEM value="S27273M Extended Use w/l Phone Batt."/>
  <LISTITEM value="64MB Memory Card"/>
  <LISTITEM value="128MB Memory Card"/>
  <LISTITEM value="256MB Memory Card"/>
  <LISTITEM value="Bounce"/>
  <LISTITEM value="Endurance Racing"/>
  <LISTITEM value="Smash up Boxing"/>
  <LISTITEM value="Martial Arts Champions"/>
  <LISTITEM value="Comic Book Heroes"/>
  <LISTITEM value="Fly Fishing"/>
  <LISTITEM value="Finding Fido"/>
  <LISTITEM value="Adventures with Numbers"/>
  <LISTITEM value="Extension Cable"/>
  <LISTITEM value="Xtend Memory"/>
</GLOBALLIST>


SQL> 


SY.
Re: issue inserting multiple lines into one row [message #621347 is a reply to message #621345] Tue, 12 August 2014 12:43 Go to previous messageGo to next message
martinez77
Messages: 4
Registered: August 2014
Junior Member
Solomon,

you are correct and I had no idea there were already tags for this.

This is perfect! thank you so much!!!!

Is there a way I can use the xml tags to insert the xml into a table?

Also this is the 5th forum I have tried and you are the only person to know that for your info.


Best,
Martinez

[Updated on: Tue, 12 August 2014 12:50]

Report message to a moderator

Re: issue inserting multiple lines into one row [message #621348 is a reply to message #621347] Tue, 12 August 2014 12:58 Go to previous message
Solomon Yakobson
Messages: 2060
Registered: January 2010
Senior Member
martinez77 wrote on Tue, 12 August 2014 13:43
Is there a way I can use the xml tags to insert the xml into a table?


Not exactly sure what you mean by "tags", but it looks like to want to insert XML data into relational table. Ifso, you could use XMLTABLE function. There are plenty of examples on this forum.

SY.
Previous Topic: Find table with column
Next Topic: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT
Goto Forum:
  


Current Time: Fri Nov 21 21:21:09 CST 2014

Total time taken to generate the page: 0.09587 seconds