Home » SQL & PL/SQL » SQL & PL/SQL » XML tag selection (ORACLE 11g)
XML tag selection [message #654039] Tue, 26 July 2016 04:23 Go to next message
lokimisc
Messages: 101
Registered: February 2008
Senior Member
Hi,
I have xml data (XMLTYPE column) in table where tags of fields are not in same case ( lower or upper).
So I want to select only tags in xml and convert into lowercase.
Could anyone please help in achieving it.

Note: I used below code to convert tag fields into lower case, but when used on column xmltype it is throwing below error.

ORA-19011: Character string buffer too small
19011. 00000 - "Character string buffer too small"
*Cause: The string result asked for is too big to return back
*Action: Get the result as a lob instead

with t
    as
    (
     select '<EffeCTIveDate>01/01/2015</effeCTiveDate>' str from dual
     union all
     select '<description>TESTING xml</DescripTion>' str from dual
     union all
     SELECT '<projectNumber>311879</projectNumber>' from dual
     UNION ALL
     SELECT '<Test/>' FROM DUAL
    )
select lower(regexp_substr(regexp_substr(str,'[^<]+',1,1),'[^/>]+',1,1)) FROM t ;



Thanking You.

Regards,
Lokesh
Re: XML tag selection [message #654053 is a reply to message #654039] Tue, 26 July 2016 07:55 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
Are you sure that you are storing data where the tags do not match, as an xmltype? I'm not an expert with xml, but I'm pretty sure that that wouldn't be allowed:

select xmltype('<base>
                   <tag1>1</tag1>
                   <tag2>2</Tag2>
                </base>')
from dual;

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "Tag2" does not match start-element tag "tag2"
Error at line 3
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

Maybe I've picked you up wrong.
Re: XML tag selection [message #654054 is a reply to message #654053] Tue, 26 July 2016 08:18 Go to previous messageGo to next message
lokimisc
Messages: 101
Registered: February 2008
Senior Member
Thanks pablolee for your response.
Issue is not about storing xml data (i.e is valid xml data is stored in xmltype datatype), but when trying to find the tag field name and converting case to lower or upper is throwing error.
In short I require SQL which list or selects all tag field names of xml data stored in table.
I hope you are clear now, if you still have question, please let me know.

Thanks,
Lokesh
Re: XML tag selection [message #654055 is a reply to message #654054] Tue, 26 July 2016 08:33 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
lokimisc wrote on Tue, 26 July 2016 14:18
Thanks pablolee for your response.
Issue is not about storing xml data (i.e is valid xml data is stored in xmltype datatype),
Then in that case, the sample that you supplied is incorrect as having mismatching tags would not be valid. Are you saying that in reality the opening and closing tags do in fact match in your data?

Re: XML tag selection [message #654057 is a reply to message #654055] Tue, 26 July 2016 08:54 Go to previous messageGo to next message
lokimisc
Messages: 101
Registered: February 2008
Senior Member
Hi,
Please check below for the code.
CREATE TABLE xmldata
(
 xid NUMBER,
 xname VARCHAR2(20),
 xdata CLOB
 );

 INSERT INTO xmldata
 values (1,'Parse xml', '<?xml version="1.0"?>
<company>
        <Employee>
                <FirstName>Tom</firstname>
                <LastName>Cruise</LASTNAME>
        </Employee>
        <employee>
                <FirstName>Paul</FIRSTNAME>
                <LastName>Enderson</lastname>
        </employee>
        <Employee>
                <FirstName>George</firstname>
                <LastName>Bush</lastname>
        </EmployEE>
</company>');

With above example I need query which selects all tag field names in xdata column and convert to lowercase.
The query which I provided was sample which was able to select field name but when applied same logic to column value it was not working.

Please let me know, if any clarification required.

Regards,
Lokesh
Re: XML tag selection [message #654058 is a reply to message #654057] Tue, 26 July 2016 09:01 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
So you're not storing the data in an XMLTYPE column (as you stated in your earlier post), is that correct?

[Updated on: Tue, 26 July 2016 09:07]

Report message to a moderator

Re: XML tag selection [message #654059 is a reply to message #654057] Tue, 26 July 2016 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66046
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want all tags in lower case (but I'm not sure this what you want), you can do something like:
SQL> with
  2    data as (select
  3  '<?xml version="1.0"?>
  4  <company>
  5          <Employee>
  6                  <FirstName>Tom</firstname>
  7                  <LastName>Cruise</LASTNAME>
  8          </Employee>
  9          <employee>
 10                  <FirstName>Paul</FIRSTNAME>
 11                  <LastName>Enderson</lastname>
 12          </employee>
 13          <Employee>
 14                  <FirstName>George</firstname>
 15                  <LastName>Bush</lastname>
 16          </EmployEE>
 17  </company>' val from dual
 18    ),
 19    tags as (
 20      select regexp_substr(val,'<[^>]+>',1,column_value) tag
 21      from data,
 22           table(cast(multiset(select level from dual
 23                               connect by level <= regexp_count(val,'<')+1)
 24                 as sys.odciNumberList))
 25    )
 26  select distinct rtrim(ltrim(lower(tag),'</'),'>') tag
 27  from tags
 28  where tag is not null and tag not like '<?%'
 29  /
TAG
------------------------------------------------------------------------------
company
firstname
employee
lastname

4 rows selected.
Re: XML tag selection [message #654105 is a reply to message #654059] Wed, 27 July 2016 01:58 Go to previous messageGo to next message
lokimisc
Messages: 101
Registered: February 2008
Senior Member
Thanks Michel for the solution... Smile
Re: XML tag selection [message #654107 is a reply to message #654105] Wed, 27 July 2016 02:09 Go to previous message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
lokimisc wrote on Wed, 27 July 2016 07:58
Thanks Michel for the solution... Smile

Next time, try to more accurately represent your situation, it will reduce the number of clarifications required.
Previous Topic: Retrieve text for ID from another table for two columns in a SELECT statement
Next Topic: unusable index on inserting data into partition table
Goto Forum:
  


Current Time: Tue Dec 11 11:42:20 CST 2018