This discussion is archived
6 Replies Latest reply: Jun 5, 2013 7:50 AM by Jason_(A_Non) RSS

XML query

986217 Newbie
Currently Being Moderated
How to extract Users.Display Name and get its value like testfuturedate102, Mr. user

by modifying the below mentioned query

select
xmltype(mytext).extract('//Attribute').getStringval() code1
from (
select
'
<UserProfileSnapshot key="224" version="1.0">
<UserInfo>
<Attribute name="Users.Display Name">testfuturedate102, Mr. user</Attribute>
<Attribute name="USR_UDF_HR_ORG_ID">202</Attribute>
<Attribute name="Users.Xellerate Type">End-User</Attribute>
<Attribute name="Users.Role">Employee</Attribute>
<Attribute name="Users.First Name">user</Attribute>
<Attribute name="Users.Status">Active</Attribute>
<Attribute name="USR_UDF_LOCATION_ID">1737</Attribute>
<Attribute name="Users.Provisioned Date">2013-04-23 10:04:48 -0400</Attribute>
<Attribute name="Title">MR.</Attribute>
<Attribute name="USR_UDF_LOCATION_CODE">HR- New York</Attribute>
<Attribute name="Country">US</Attribute>
<Attribute name="State">NY</Attribute>
<Attribute name="Users.Password Reset Attempts Counter">0</Attribute>
<Attribute name="Users.Disable User">0</Attribute>
<Attribute name="Users.Change Password At Next Logon">1</Attribute>
<Attribute name="USR_CN_GENERATED">0</Attribute>
<Attribute name="USR_UDF_EFFECTIVE_START_DATE">2013-03-08 00:00:00 -0500</Attribute>
<Attribute name="Postal Code">10001</Attribute>
<Attribute name="Users.Update Date">2013-04-23 10:04:48 -0400</Attribute>
<Attribute name="Employee Number">2335</Attribute>
<Attribute name="USR_UDF_DEPARTMENT_NAME">Vision Corporation</Attribute>
<Attribute name="Users.Last Name">testfuturedate102</Attribute>
<Attribute name="Users.End Date">4712-12-31 00:00:00 -0500</Attribute>
<Attribute name="Hire Date">2013-02-28 00:00:00 -0500</Attribute>
<Attribute name="USR_UDF_PERSON_ID">31967</Attribute>
<Attribute name="USR_UDF_IS_MANAGER">N</Attribute>
<Attribute name="Users.Creation Date">2013-04-23 10:04:48 -0400</Attribute>
<Attribute name="Street">500 Madison Ave</Attribute>
<Attribute name="Users.User ID">31967</Attribute>
<Attribute name="USR_UDF_CITY">New York</Attribute>
<Attribute name="Users.Lock User">0</Attribute>
<Attribute name="Users.Updated By Login" key="4">OIMINTERNAL</Attribute>
<Attribute name="Users.Created By Login" key="4">OIMINTERNAL</Attribute>
<Attribute name="Users.Login Attempts Counter">0</Attribute>
<Attribute name="Organizations.Organization Name" key="4">Integra</Attribute>
</UserInfo>
</UserProfileSnapshot>
' mytext
from dual
) x
  • 1. Re: XML query
    odie_63 Guru
    Currently Being Moderated
    You might be using deprecated functions. What's the db version?

    Use at least extractValue() function to handle possible escaped entities in the value :
    extractvalue( xmltype(mytext), '/UserProfileSnapshot/UserInfo/Attribute[@name="Users.Display Name"]' )
  • 2. Re: XML query
    986217 Newbie
    Currently Being Moderated
    HI,

    Thanks for replying.
    I have one more question.

    This data is present in clob i need to extract the below mentioned field.
    Can you please help me in making a query for this.

    "<?xml version = '1.0' encoding = 'UTF-8'?>
    <UserProfileSnapshot key="224" version="1.0">
    <UserInfo>
    <Attribute name="Users.Display Name">testfuturedate102, Mr. user</Attribute>
    <Attribute name="USR_UDF_HR_ORG_ID">202</Attribute>
    <Attribute name="Users.Xellerate Type">End-User</Attribute>
    <Attribute name="Users.Role">Employee</Attribute>
    <Attribute name="Users.First Name">user</Attribute>
    <Attribute name="Users.Status">Active</Attribute>
    <Attribute name="USR_UDF_LOCATION_ID">1737</Attribute>
    <Attribute name="Users.Provisioned Date">2013-04-23 10:04:48 -0400</Attribute>
    <Attribute name="Title">MR.</Attribute>
    <Attribute name="USR_UDF_LOCATION_CODE">HR- New York</Attribute>
    <Attribute name="Country">US</Attribute>
    <Attribute name="State">NY</Attribute>
    <Attribute name="Users.Password Reset Attempts Counter">0</Attribute>
    <Attribute name="Users.Disable User">0</Attribute>
    <Attribute name="Users.Change Password At Next Logon">1</Attribute>
    <Attribute name="USR_CN_GENERATED">0</Attribute>
    <Attribute name="USR_UDF_EFFECTIVE_START_DATE">2013-03-08 00:00:00 -0500</Attribute>
    <Attribute name="Postal Code">10001</Attribute>
    <Attribute name="Users.Update Date">2013-04-23 10:04:48 -0400</Attribute>
    <Attribute name="Employee Number">2335</Attribute>
    <Attribute name="USR_UDF_DEPARTMENT_NAME">Vision Corporation</Attribute>
    <Attribute name="Users.Last Name">testfuturedate102</Attribute>
    <Attribute name="Users.End Date">4712-12-31 00:00:00 -0500</Attribute>
    <Attribute name="Hire Date">2013-02-28 00:00:00 -0500</Attribute>
    <Attribute name="USR_UDF_PERSON_ID">31967</Attribute>
    <Attribute name="USR_UDF_IS_MANAGER">N</Attribute>
    <Attribute name="Users.Creation Date">2013-04-23 10:04:48 -0400</Attribute>
    <Attribute name="Street">500 Madison Ave</Attribute>
    <Attribute name="Users.User ID">31967</Attribute>
    <Attribute name="USR_UDF_CITY">New York</Attribute>
    <Attribute name="Users.Lock User">0</Attribute>
    <Attribute name="Users.Updated By Login" key="4">OIMINTERNAL</Attribute>
    <Attribute name="Users.Created By Login" key="4">OIMINTERNAL</Attribute>
    <Attribute name="Users.Login Attempts Counter">0</Attribute>
    <Attribute name="Organizations.Organization Name" key="4">Integra</Attribute>
    </UserInfo>
    </UserProfileSnapshot>"
  • 3. Re: XML query
    986217 Newbie
    Currently Being Moderated
    i am naive to this so please help me in finding out additional information.

    Additionally there is mistake in the first line which should be <?xml version = "1.0" encoding = "UTF-8"?> instead of <?xml version = '1.0' encoding = 'UTF-8'?> but we have limitation on changing the data.

    Please help me its very urgent.

    Edited by: 983214 on Jun 4, 2013 9:41 AM
  • 4. Re: XML query
    odie_63 Guru
    Currently Being Moderated
    983214 wrote:
    This data is present in clob i need to extract the below mentioned field.
    In a CLOB column or CLOB variable ?

    What happens when you try extractValue function on your real data?

    And you probably also missed my first question :
    What's the db version?
    Please answer all these questions and you may get a better and quicker help.
  • 5. Re: XML query
    986217 Newbie
    Currently Being Moderated
    Thanks for responding my db details are given below

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    "CORE     11.2.0.2.0     Production"
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
  • 6. Re: XML query
    Jason_(A_Non) Expert
    Currently Being Moderated
    I'll just jump in to point out that extract and extractValue were deprecated in 11.2.

    See [url http://docs.oracle.com/cd/E18283_01/appdev.112/e16659/whatsnew.htm#insertedID4]Oracle Database 11g Release 2 (11.2.0.1) Deprecated Oracle XML DB Constructs

    That's the reason that Odie's other examples for you use XMLTable. You also didn't say whether the data is in a CLOB column in a table or a CLOB variable in PL/SQL as he asked either.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points