f



xml data to columns table

Hi all,

Assuming i have this xml fragment, stored in a column named xmldata of
CLOB type, in a table
'<Warehouse>
    <WarehouseId>1</WarehouseId>
    <WarehouseName>Montreal, Quebec</WarehouseName>
</Warehouse>
<Warehouse>
    <WarehouseId>2</WarehouseId>
    <WarehouseName>Southlake, Texas</WarehouseName>
</Warehouse>
'

How can i generate in one query, the xml elements to have them in
columns table format ?

I know, i could do something like

select
extractvalue(a.xmldata,'//Warehouse/WarehouseId') id,
extractvalue(a.xmldata,'//Warehouse/WarehouseName') name
from mytable a

Result
-------
id       name
1        Montreal, Quebec
2        Southlake, Texas

and that would satisfy my needs. But, what if i have many more
xmlelements inside my xml ? I wouldn't like to select
each column by each column as above. In fact, i'm looking something
equal to the "select *" but from an xmldata so that it is directly
splitted into the right number of columns, based on the xml elements.
Is that clear for anybody ..?
Thanks for helping,
Regards,
Mike.

0
inluvwitiou
5/10/2005 10:00:02 AM
comp.databases.oracle.misc 8436 articles. 1 followers. Post Follow

0 Replies
394 Views

Similar Articles

[PageSpeed] 47

Reply:

Similar Artilces:

Tools to extract data from SQL database and convert it into XML & insert XML data into SQL databases
As the subject said I am searching for a tool to extract data from SQL databases (e.g. MySQL) and convert it to an XML document. And vice versa a tool which enables me to insert a given XML data document into a (My)SQL database? Harry "Harry Zoroc" <harryzz@vodafone.com> wrote in message news:ccuvrq$dk$06$1@news.t-online.com... > As the subject said I am searching for a tool to extract data from SQL databases (e.g. MySQL) > and convert it to an XML document. www.intsysr.com/odbc2xml.htm > And vice versa a tool which enables me to insert a given XML data documen...

How to add xml to a column in a Database table
Hello! I have defined a table like this. It's just for learning. Create table MyTable (MyIntColumn int Primary KEY, MyXmlColumn xml) The table is created fine but it's not possible to add any xml to the MyXmlColumn it's like the column is read only ? //Tony Tony (johansson.andersson@telia.com) writes: > I have defined a table like this. It's just for learning. > Create table MyTable (MyIntColumn int Primary KEY, MyXmlColumn xml) > > The table is created fine but it's not possible to add any xml to the > MyXmlColumn > it's like the column is re...

How to read every data in the first column corresponding to the every data in the second column in the TABLE CONTROL?
Hello everyone &nbsp; like this table &nbsp; 0.0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -4.6 0.0002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -4.3 0.0004&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -4.0 0.0006 0.0008 0.001 &nbsp; I want to get&nbsp; pairs (0.0001, -4.6) (0.0001, -4.3)&nbsp; (0.0001,&nbsp; -4.0) (0.0002,&nbsp; -4.6) (0.0002,&nbsp; -4.3) (0.0002,&nbsp; -4.0) (0.0004,&nbsp; -4.6) (0.0004,&n...

Newbie: Generate XML for data from Oracle Table Rows
Hi, I am very new to Oracle. My customer has a requirement, where in I need to generate the XML scripts for the data that we are appending with in the table. eg: for the table col1 col2 col3 ----------------------------------------- Rohit Arora DEV XXX ABC PROD Then the xmls should be added to another field in table TBL_AUDIT: <Tran> <Data> <Field Value="col1"> <old Val/> <New Val>Rohit</New Val> </Field> ...... ...... .......... </Data> </Tran> Is there any tool to do so automatically or any sample code as to how to do it......... It will be of immense help. Rgds, Rohit Rohit wrote: >Is there any tool to do so automatically or any >sample code as to how >to do it......... http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4980337843276 See also http://www.dbaoracle.net/readme-cdos.htm for posting clues. jg -- @home.com is bogus. New Adventures of Old Gidget: http://www.signonsandiego.com/uniontrib/20060421/news_2m21gidget.html ...

Create XML file from XMLTYPE column in oracle database
Hi All, Could someone please help me how to create xml doc from database with xmltype datatype? I want something like utl_file.put_line. Help appreciated. Thanks! lizansi@gmail.com wrote: > Hi All, > > Could someone please help me how to create xml doc from database with > xmltype datatype? > > I want something like utl_file.put_line. > > Help appreciated. > Thanks! Go to asktom.oracle.com and put in the search box: xml output jg -- @home.com is bogus. http://www.davidbowieisverydisappointedinyou.com/ ...

Create XML file from XMLTYPE column in oracle database #2
Hi All, Could someone please help me how to create xml doc from database with xmltype datatype? I want something like utl_file.put_line. Help appreciated. Thanks! ...

How to Read Oracle Database table object data using Labveiw
This is database in Oracle table named NUMBER_TESTING; &nbsp; Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Null?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type NUMBER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(20)DATE_TIME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...

How to display database table data in a html table.
Hi, I'm trying to display data from a mysql database in a HTML table but for some reason my code isn't working. At the moment I have got it to read and display the headers and the first row of the table and it actually creates the remaining rows in the html table but it doesn't put any data in them. This is my code so far: <?php $con = mysql_connect("localhost","REMOVED","REMOVED"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("db_03009319", $con); ?> <?php $selectedTable=$_GET["sel...

Copy column of data into another column in the same table
I have a column of digits I'd like to copy into another column in the same table. How would I do this? Thanks, Bill On 18 Jul 2003 11:06:33 -0700 in comp.databases.ms-sqlserver, billzimmerman@gospellight.com (Bill) wrote: >I have a column of digits I'd like to copy into another column in the >same table. How would I do this? update table set column1 = column2 -- Ride Free (but you still have to pay for the petrol) (replace sithlord with trevor for email) ...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril On 10 Aug 2004, jewelk@free.fr wrote: > Hello, > > I need to copy a table from an 8i oracle database to a > sqlserver 2000 database. A few options exist. If this is one-off, just use sqlldr to drop the data to a file and then bcp to get it into SQLServer. > Is it possible to use the command "COPY FROM ... TO ..." ? > So, ...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100617.6f7b9f3e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? > > > Thanks for your help > &...

DB2 - XML and Spatial data in same column - can't alter table once created
I'm testing out db2 for a project I'm starting that requires proper xml support and I can't seem to get both xml and spatial data to work well in the same table. Once having created a table containing both xml and spatial data fields I can't seem to find a way to alter the table I have created a table containing an id, xmldata field and a ST_Point If I try to drop the xml field with ALTER TABLE TEST DROP COLUMN XMLDATA I get an error message saying alter table etest drop column xmldata DB21034E The command was processed as an SQL statement because it was no...

How to insert/update a column with text data with other columns in a table?
Hi, How should we insert/update a (text data) column in a table? I have a table &#8220;Server&#8221; (Server Name, IP Address, System_Info). The &#8220;System_Info&#8221; column contains several rows of data. On the other side the &#8220;Server Name&#8221; & &#8220;IP Address&#8217; column contain only one row. I was trying to insert/update the &#8220;System_Info&#8221; column via SQLLOADER but I didn&#8217;t get the desired results. Could some one suggest how should we insert or update the column like &#8220;System_Info&#8221; in a ...

Oracle column data types ane .NET data types
I am working with an Oracle database and am using ADO .NET to read in column values from a row into .NET data types. How can I determine at compile time, for a given Oracle column data type what is the corresponding .NET data type ? Is this in some Oracle documentation for using Oracle in ADO .NET ? Is it in some Microsoft documentation for using Oracle with ADO .NET ? I have not been able to find it anywhere as yet. Edward Diener wrote: > I am working with an Oracle database and am using ADO .NET to read in column > values from a row into .NET data types. How can I determ...

Oracle 10g
Hi! I would like to find an application which allows me to read data from a database (object-relational database schema), display it visually, make it editable and offer me the opportunity to write changed data to the database. Does anyone have experience with Visio or some other tool? Timo Toad is a good choice. Try google. You'll find it. Umberto "timoomit" <timo.flad@octocon.de> wrote in message news:1146045907.917129.126700@t31g2000cwb.googlegroups.com... : Hi! : : I would like to find an application which allows me to read data from a : database (object-relational database schema), display it visually, make : it editable and offer me the opportunity to write changed data to the : database. : : Does anyone have experience with Visio or some other tool? : : Timo : Application Express (Formerly HTML-DB) Comes with the database. [++ mcs] How about SQL Developer, available for free from Technet (http://technet.oracle.com). HTH, Brian -- =================================================================== Brian Peasland oracle_dba@nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown "Brian Peasland" <oracle_dba@nospam.peasland.net> wrote in message news:IyBzLn.6B9@igsrsparc2.er.usgs.gov... : How about S...

Distinguish committed data from other transactions and uncommitted data from my transaction in a database table.
Hello, I have the following problem: I have an interace table ordedata with a a sequence number to determine the sequence of the data. The table looks like this: ordecode orderow, ... seqnbr (sequence number ) I have now a transaction, which writes one or several rows into that table. What I want is the following: At the beginning of the transaction I am selecting the highest sequence number for the data for a specific order and store it in a global PL/SQL-Variable. At the end of the transaction, I want to get again the highest sequence number in the table for my order. Now I want to fin...

load xml data into Access where an xml node has > 256 columns
I have an XML download I want to load to Access. I can do this for most of the sub-nodes of the xml document. However, one node has about 400 fields. I would like to break it up and store in two Access tables. Anyone done this via VBA or a utility? If VBA, care to share the code? Bob On 9/7/10 5:08 PM, Bob Alston wrote: > I have an XML download I want to load to Access. I can do this for most > of the sub-nodes of the xml document. However, one node has about 400 > fields. I would like to break it up and store in two Access tables. > > Anyone done this via ...

How to get database metadata information (i.e. existing tables and columns in tables)
Hi, Is it possible to retrieve details about the database, specifically a list of the tables in the database; and then to retrieve the columns and their types for the tables? Is this dependant on the database? Thanks Chris Chris Brat wrote: > Is it possible to retrieve details about the database, specifically a > list of the tables in the database; and then to retrieve the columns > and their types for the tables? Yes. > Is this dependant on the database? Yes. Real databases usually store meta-data (list of tables, list of columns, list of indexes aso...) in system tab...

XMl-data in database
Hi, i have to convert an database with the data of an xml-file throught a matlab-gui. Has anyone an idea how to dow it? thx melli ...

Updating data in table depending on data in same table
Hey everyone, First of all apologies for posting this in the oracle.server section I meant to post it in here first. I'm still a relative newb so go easy please! I've inherited a system from someone that is in a bit of a mess and I need to tidy it up. It's an Oracle 10g database. The part i have to clean up basically has two tables. Table A attributes are: SOURCEID, NAME, DESCRIPTION, DEPARTMENT, LANGUAGES. Table B attributes are: SOURCEID, LANGID, ROLE, STATUS. Now the way this system is supposed to work is that a row is entered into table A and X amount of languages are added to the LANGUAGES field. This field is then split up into a row for each language into table B with the corresponding SOURCEID. e.g. Table A: 123 -- Homer -- lazy -- HR -- French;Spanish;German Table B: 123 -- French -- manager -- approved 123 -- Spanish -- admin -- pending 123 -- German -- teaboy -- proposed This system had been working fine until a new person started and instead of just adding a new language to the original row in table A she added 1000+ rows that are identical to those already in the table except for the fact the language is Japanese. So now the table looks like Table A: 123 -- Homer -- lazy -- HR -- French;Spanish;German 456 -- Homer -- lazy -- HR -- Japanese; Table B: 123 -- French -- manager -- approved 123 -- Spanish -- admin -- pending 123 -- German -- teaboy -- proposed 456 -- Japanese -- janitor -- approved I have...

database data to XML
I'm working on a site a part of which involves personalized data, which I'm sure has been done to death, just not by me! anyway, as I am going about designing this system, here is what I'm wondering about. database < ------converted to xml pages ---> into xml cache directory data xml files <------ transformed to html ---> html to browser What I am wondering about is this: when is the data from the database converted to xml typically ? It seems to me an offline batch process is probably the way to go. * What about xslt to html ? when is this typcally done ? I kno...

DataBase Table Columns
Is there a max. Number of columns that can be used in a Table using 5.5.3 ? I know with 5.2 if I had more than 15 columns in a table the whole database became unstable I was just wondering if that still holds true or if 100 columns in a table is as stable as just 1 column. Thanks In article <c7-dnTLcOu05_A_cRVn-qg@rogers.com>, "JM" <no@Spam.tu> wrote: > Is there a max. Number of columns that can be used in a Table using 5.5.3 ? No. > I know with 5.2 if I had more than 15 columns in a table the whole database > became unstable I was ju...

"HOW TO MIGRATE THE MS-ACCESS TABLE TO ORACLE DATA BASE TABLES"
Hi to group, I'm a student of M.C.A. from B.I.T. Ranchi...I'm in my project period here i have to migrate the MS-Access database table to Oracle data base tables...so, please help me in getting this....if possible plz send me the sample code.. thanking you, Not that straight forward I m afraid - Think in terms of Access as a database which is part of the MS OFFice application suit - Think of Oracle in terms of a mainfraime. Oracle is a totally different Architecture. You will need SQL loader within Oracle - You have to create a text control file which has SQL code that will t...

tables for data from databases
Hello Does it exist any class that draw a table and put data from database to that table ? How can i easily (using which class) show table from database ? Thanx Michal highland: > Does it exist any class that draw a table and put data from database >to that table ? How can i easily (using which class) show table from >database ? Here's code that displays SQL query results in a JTable: <http://www.oreillynet.com/pub/a/oreilly/java/news/javaex_1000.html>. Regards, Marco -- Please reply in the newsgroup, not by email! Java programming tips: http://jiu.sourceforge...

Web resources about - xml data to columns table - comp.databases.oracle.misc

Politics, Political News - The Capitol Column
Covering the politics of science, technology, business, entertainment, Capitol Hill, and health.

Column vector - Wikipedia, the free encyclopedia
... unclear because it lacks inline citations . Please improve this article by introducing more precise citations. In linear algebra , a column ...


Column 8
... Grinter, of Katoomba, is one of several readers demanding an explanation of the word &quot;hemiteleiated&quot; (&quot;No wuckers&quot;, Column ...

Price Drop: Column Addition
Column Addition 3.0 Device: iOS Universal Category: Education Price: Free, Version: 3.0 ( iTunes ) Description: This math app can be used ...

Column: Glenn Frey's legacy extends to Aspen Junior Golf
Aspen Times Column: Glenn Frey's legacy extends to Aspen Junior Golf Aspen Times Rohrbaugh, one of Colorado's most decorated PGA club pros, ...

ESPN public editor Jim Brady’s Grantland column comes under fire from former staffers
... 30, but its impact is still being felt in sports media. That's most recently the case with ESPN public editor Jim Brady's long-promised column ...

Filling some column space
So, um, guess we still have a blog here. Story time – when I was twelve or thirteen one of the three networks kept putting this movie called ...

Jeremy Clarkson blames Twitter for the backlash over his column about transgender people
Jeremy Clarkson has blamed Twitter for the backlash over his column in The Sunday Times that appeared to deride transgender people. In a new ...

Nitin Desai: Budget choices - Business Standard Column New Document Widgets Magazine Widgets Magazine ...
The finance minister faces a difficult problem in choosing between two sets of advisers. Both factions accept that the Budget must aim at stimulating ...

Resources last updated: 2/20/2016 2:11:14 PM