Extract XML data based on column tag - XML (Korean)

Dear All, i want to extract the XML data type based on column selection. For example: i have the below xml in my database stored as XMLTYPE() data.  <c1>10</c1><c2>0011</c2><c3>DEBIT</c3><c4>USD</c4> i want to extract the column tag c2&c3 alone. Is there any query to extract the same. Waiting for your response.Thanks in Advance.

Related

Data retrieval from a table which contains a column of long raw data type

we have a table in which one of the columns is of datatype log raw. we would like to know how do we retrieve the information and display it in a form / report. The problem we are facing is when we retrieve the information using select statement we get binary data !

Using a SQL data source and XML data source in the same template

How can I extend the xml template fields by using SQL query referring to existing data source fields?
Example:
Suppose that we have XML template displaying employee number and name.
I want to extend this template by adding nationality field using SQL statement that retrieves the nationality for each employee.
Note:
I want to do it by using SQL statement and I do not want to change the XML data source 
The problem statement is not very clear, it is somewhat clear.
Are you saying that you have a SQL query which "select"s the emp_id and name? In the same XML that is created by this, you want also another field: country?
I think, in that case, using data templates will solve the problem.
Using data templates, you can fire 2 SQL queries (one for emp_id/name, and the other for country) and then 1 XML file will be generated with this data. 
Are there any examples for using 2 or more sql queries like this ??
thx Carl

CLOB TO VARCHAR/NUMBER

I have an oracle table with a CLOB Column.
This column contains XML data.
I want to extract individual values from this CLOB column and load them into another target table containing normal columns with datatypes like number and varchar.
i want to do this using OWB Mapping.
Can anyone please help me that how can this be done. 
Check this:
extracting from XML stored as clob

What is the best way to load XML data file in to oracle apps table.

Hi All,
Instance: Oracle apps R12.
I am looking for best way to load XML file data in to apps table.
For Ex: one xml file has 100 employee records. how to load those 100 emp in to some custom table with emp no, emp name, sal as its columns.
This can be of two types. One category of emp would be first time employees for which a record should be created and seond may be updating the existing emp salary.
Please suggest.
Thank you. 
If you can create a separate list for the new employees and the update, you might want to use SQL Loader.
But this utility is unable to update existing rows in the table.
If you want the loading to happen once, you will have to use PL/SQL. 
you can load them using XMLTYPE
Create a column with XMLTYPE and load xmldata 
XMLTYPE is only good for storing the entire XML file into a single cell.
If this is an ongoing need and you know the XML is a predefined template, you could use the Oracle Rules Manager and Expression Filter to define your XML template and load data using custom PL/SQL script into the target table. 
I think (depending on your db version) that it is possible to use an xml formatted file as an external table. 
Hi,
If the data structure is linear flat (which it should be since your are loading to EDBMS table) I will urge open the xml file in excel and save as xls.
Then using the database-> import property of toad load the data in to Oracle Apps Table.

How do i transform XML as a string to XML

Hi, I have a DB table where a whole XML is stored in a column as a string.I was a DO based on XSD which is of Same XML Structure. When i poll this table using a DBadapter, this column is returned as String.How i can achieve this transformation using XSL. I tried doTranslateFromNative Please help.

Categories

Resources