Home » Developer & Programmer » JDeveloper, Java & XML » Selecting the attribute value of given xmltype (10.2.0.4.0 - 64bi)
Selecting the attribute value of given xmltype [message #535160] Mon, 12 December 2011 08:16 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi ,
I need to get the value of an attribute for the given attribute name of xmltype. Please help me

DROP TABLE  some_things ;

create table some_things (object_value CLOB);

insert into some_things values
('<?xml version="1.0" encoding="UTF-8"?>
<indexAttributes>
    <attribute name="PENSION_APPLICATION.APPLICATION_ID" value="App123 "/>
    <attribute name="PERSON.ALT_IDENTIFIER" value="9046095052"/>
    <attribute name="PERSON.SSN" value="741852963 "/>
</indexAttributes>');

insert into some_things values
('<?xml version="1.0" encoding="UTF-8"?>
<indexAttributes>
    <attribute name="PENSION_APPLICATION.APPLICATION_ID" value="App256 "/>
    <attribute name="PERSON.ALT_IDENTIFIER" value="9046095052"/>
    <attribute name="PERSON.SSN" value="859632147 "/>
</indexAttributes>');



select extractValue(sys.xmltype.createXML(object_value),
                    '/indexAttributes.PERSON.SSN') ssn , 
                    extractValue(sys.xmltype.createXML(object_value),
'/indexAttributes.PENSION_APPLICATION.APPLICATION_ID')APPLICATION_ID
  from some_things;

select *  from   some_things;



Now i need to get the application id and the corresponding ssn number. I tried by using extractValue function. But it's not retuning any value

Thanks
Sai pradyumn




[Updated on: Mon, 12 December 2011 08:22] by Moderator

Report message to a moderator

Re: Selecting the attribute value of given xmltype [message #535162 is a reply to message #535160] Mon, 12 December 2011 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col ssn format a10
SQL> col id format a10
SQL> select extractValue(value(x),'//attribute[@name="PERSON.SSN"]/@value') ssn,
  2         extractValue(value(x),'//attribute[@name="PENSION_APPLICATION.APPLICATION_ID"]/@value') id
  3  from some_things, table(xmlsequence(extract(xmltype(object_value),'/indexAttributes'))) x
  4  /
SSN        ID
---------- ----------
741852963  App123
859632147  App256

Regards
Michel
Re: Selecting the attribute value of given xmltype [message #535163 is a reply to message #535162] Mon, 12 December 2011 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I overcomplicated it:
SQL> select extractValue(xmltype(object_value),
  2                      '//attribute[@name="PERSON.SSN"]/@value') ssn,
  3         extractValue(xmltype(object_value),
  4                      '//attribute[@name="PENSION_APPLICATION.APPLICATION_ID"]/@value') id
  5  from some_things
  6  /
SSN        ID
---------- ----------
741852963  App123
859632147  App256

Regards
Michel
Re: Selecting the attribute value of given xmltype [message #535190 is a reply to message #535160] Mon, 12 December 2011 14:16 Go to previous messageGo to next message
transfer
Messages: 53
Registered: August 2007
Location: transfer
Member
Simply for information, starting in version 11G Oracle suggests using XMLTABLE for this, although Michel's solution is just fine. For anyone interested in an XMLTABLE variant:
SELECT APPLICATION_ID, SSN
FROM SOME_THINGS, XMLTABLE (
  '$X' PASSING xmltype(object_value, null, 1, 1) AS X 
  COLUMNS 
    APPLICATION_ID VARCHAR2(63) PATH '//attribute[@name="PENSION_APPLICATION.APPLICATION_ID"]/@value',
    SSN INT PATH '//attribute[@name="PERSON.SSN"]/@value'
);
I shamelessly copied the PATH expressions from Michel. Notice that XMLTABLE lets you define the datatype: SSN is converted to an integer, whereas EXTRACTVALUE returns VARCHAR2 unless you reference an XML schema.
Re: Selecting the attribute value of given xmltype [message #535191 is a reply to message #535190] Mon, 12 December 2011 14:53 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the alternate solution, I, as OP, most still use 10gR2.

Regards
Michel
Previous Topic: Not Able to see OA framework workspace
Next Topic: whats wong with this??
Goto Forum:
  


Current Time: Thu Mar 28 19:09:37 CDT 2024