Converting each row in a column to an XML
Using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
I have a table where there is a comments column which is VARCHAR2. That column contains string data in the form of XML tags.
Like for eg. *<tag_name attr_name="10"/>* (sorry cant provide the exact values as I'm not allowed to)
There are many such rows. Now I want to use these strings and operate on them as XML like using XPath to find certain attributes etc.
I tried using the extract function and to use it i need a XMLType object. So I tried to create one using the XMLType() constructor.
But it gives me the error ORA-19032: Expected XML tag , got no content
When I looked it up on the net, I found that this error number doesn't correspond to the error message that I'm getting. The error msg found on the internet was Expected XML tag string got string
I used this query
SELECT XMLType (<column>) FROM <table>;But when I modified it (based on someone's hunch) to
SELECT XMLType (<column>)
WHERE ROWNUM <= 1;The query worked fine. Like it can take only one row.
I also tried like this
SELECT XMLType ('<tag_name attr_name="10"/>')
FROM DUAL;This too worked fine.
So finally this has left me completely confused. Is there a way to convert every row in a column to an XMLType object so that I can use the extract function to gather information about it.
For the time being I have used REGEXes to write the code. But having the flexibility of xml would be better.
Any help would be appreciated.
Edited by: Arijit Kanrar on May 23, 2013 5:27 AM
The error message is correct. What you found is merely the generic message with string placeholders.
The error message is also pretty self-explanatory : you can't pass an empty string (NULL) to the XMLType constructor.
SQL> select xmltype('') from dual;
ORA-19032: Expected XML tag , got no content
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
no rows selectedYou have to either add a WHERE clause to filter out NULL columns, or use a CASE statement to only convert strings that aren't empty :
SELECT XMLType (<column>)
WHERE <column> IS NOT NULL ;
SELECT CASE WHEN <column> IS NOT NULL THEN XMLType (<column>) END
FROM ...And do not use EXTRACT if you want to access scalar values, use EXTRACTVALUE instead :
SQL> select extractvalue(xmltype('<tag_name attr_name="10"/>'), '/tag_name/@attr_name') from dual;
i want to convert on row to multiple column in smartform.As number of column is not per define..Please suggest the way out in smartformI saw a post and working perfect. The link is given below. http://scn.sap.com/community/abap/blog/2013/10/06/the-ca
I have tried but came to an stop... How do I convert 8 Rows by 2 Columns to 2 Rows by 8 Columns? as shown in the vi attached Thank you very much... Solved! Go to Solution. Attachments: Eight Rows Two Col convert to two Rows Eight Col.vi 8 KBJohnMc19
How do in convert a row of data into column of data to display as shown below: Column1|Column2|Column3|Column4 C1 | C2 | C3 | C4 C5 | C6 | C7 | C8 How to convert the above default behavior to
I am trying to convert data from multiple rows into multiple columns. Let me see if I can paint the picture for you. Here is a sample of the table i am trying to read from: Company Name Account 1 Sam 123 1 Sam 234 1 Joe 345 1 Sue 789 1 Sue 987 1 Sue
I have 10g, so unpivot is unavailable to me. I have a query that looks like: with data as ( select 1,2,3 from dual ) select * from data which returns 1,2,3. I need it to return: 1 2 3 and be able to name the output column. The results of this will be
DB : 188.8.131.52 OS : Solaris Sparc 5.10 I have one query which is joining few tables and giving me output like below. personnum orgnm ======= ======= 6 Keyholder 9 Sales 3 Mgmt I would like to convert that into single column like below. col
Hello guys, I'm using Oracle 10g in a windows platform, I have the following data: with test as select '000-ME-001' tag_number, 'Capacity 150' tag_details1, '000-TS-M-226' tag_details2 from dual union all select '000-ME-001' tag_number, 'Capacity 250
Hi All i have a result set like prd_d 2011 2012 what i need is prd_d_one prd_d_two 2011 2012 thanksYou can use PIVOT: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx More likely you need dynamic (data-driven) PIVOT n
hello all, i have a table with the following data Machine Time MAC1 - 1 MAC2 - 2 MAC3 - 3 MAC4 - 4 MAC5 - 5 and want to have a query as only for the mac1, mac2 and mac3 MAC1 MAC2 MAC3 1 2 3 how to acheived this...i am on 8i thanks in adv. KKSELECT SU
Hi I know there have been similar threads however I haven't found a suitable solution from them. I'm using Oracle 9i and trying to convert multiple rows to multiple columns. CREATE TABLE TEST_VALUES CUSTOMER_ID NUMBER(10), VAL VARCHAR2(100) INSERT IN
Hii I have wierd query. But not sure of how to get result out of it!!! My query returns two rows for a query that i have posed. Current Result 10 20 Expected Result 10 20 My problem here is i wanted the above two values to be stored in two variables
Hello- I have a table with 5 columns contains distinct rows. Example: COL_A COL_B COL_C COL_D COL_E 1051 25000 7/17/2009 7/20/2009 190409949A 1051 V4581 12/9/2009 12/11/2009 407829924A 1051 78659
Hi We have one requirement where we have to show the row value to column level value for Example: Source file will be in below format Component Name :101 **Batch # **100% Mod 200% Mod 300% Mod 400% Mod Tensile Elongation
Hi All, I am having a table address with the following data. ID REL_NAME REL_RELATION REL_PHONE 1--- kish_rel1--- wife--- 1111 1--- kish_rel2--- sister--- 2222 1--- kish_rel3--- brother---
there are two table in scott schema emp: empno ename deptno 12121 dfsaf 10 78782 adfsd 20 dept: deptno 10 marketing 20 finance 30 advertising i want the out put like this deptno10 deptno 20 empno 121211 41744 means to convert the row of dept table in
I need to convert PDF to Excel, however, columns and tabs make many merged cells and many blank columns. In addition to not separate the columns correctly, I see many not separate lines together in the same cell. I'm even thinking that Adobe Acrobat
CREATE TABLE #ids (empid VARCHAR(200)) INSERT INTO #ids SELECT '100,200,300,400' INSERT INTO #ids SELECT '1100,1200,1300,1400' I am trying to get output following format ID_1 ID_2 ID_3 ID_4 100 200 300 400 1000 1200 1300 1400 Eac
Hi All, I am using oracle database 11g and i have a two tables table_1, table_2 table_1 having columns emp_no first_name middle_name last_name email and table_2 having columns emp_no phone_type phone_number and having entires emp_no phone_type phone_
On ALV report there is 1 column I want to put Icon on every row of that column.That ALV program uses object oriented concept all class and methods. I want to use that icon on that row. That icon name is ICON_OKAY In my program when I assign value