4 Replies Latest reply: Jun 7, 2013 4:10 AM by 986217 RSS

    Query required for xml processing.

    986217
      This data is present in clob i need to extract the below mentioned field.

      expected query is
      select
      extractvalue( xmltype(table1.column1), '/UserProfileSnapshot/UserInfo/Attribute[@name="Users.Display Name"]' )
      from table1

      first line is incorrect <?xml version = '1.0' encoding = 'UTF-8'?>
      Can anyone please help me in making a query for this incorrect clob data.

      As we cannot change this first line in data.

      "<?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>"
        • 1. Re: Query required for xml processing.
          odie_63
          983214 wrote:
          first line is incorrect <?xml version = '1.0' encoding = 'UTF-8'?>
          What's wrong with this line?

          What error do you get?
          • 2. Re: Query required for xml processing.
            986217
            ORA-06502: PL/SQL: numeric or value error
            ORA-06512: at "SYS.XMLTYPE", line 272
            ORA-06512: at line 1
            06502. 00000 - "PL/SQL: numeric or value error%s"
            *Cause:   
            *Action:                                                                                                                                                                                                                                                                                                                                                                   
            • 3. Re: Query required for xml processing.
              odie_63
              As said in {thread:id=2545133}, the quotes are certainly not the issue.

              This works fine for me on 11.2.0.2 :
              SQL> insert into table1 (column1)
                2  values (
                3  '<?xml version = ''1.0'' encoding = ''UTF-8''?>
                4  <UserProfileSnapshot key="224" version="1.0">
                5  <UserInfo>
                6  <Attribute name="Users.Display Name">testfuturedate102, Mr. user</Attribute>
                7  <Attribute name="USR_UDF_HR_ORG_ID">202</Attribute>
                8  <Attribute name="Users.Xellerate Type">End-User</Attribute>
                9  <Attribute name="Users.Role">Employee</Attribute>
               10  <Attribute name="Users.First Name">user</Attribute>
               11  <Attribute name="Users.Status">Active</Attribute>
               12  <Attribute name="USR_UDF_LOCATION_ID">1737</Attribute>
               13  <Attribute name="Users.Provisioned Date">2013-04-23 10:04:48 -0400</Attribute>
               14  <Attribute name="Title">MR.</Attribute>
               15  <Attribute name="USR_UDF_LOCATION_CODE">HR- New York</Attribute>
               16  <Attribute name="Country">US</Attribute>
               17  <Attribute name="State">NY</Attribute>
               18  <Attribute name="Users.Password Reset Attempts Counter">0</Attribute>
               19  <Attribute name="Users.Disable User">0</Attribute>
               20  <Attribute name="Users.Change Password At Next Logon">1</Attribute>
               21  <Attribute name="USR_CN_GENERATED">0</Attribute>
               22  <Attribute name="USR_UDF_EFFECTIVE_START_DATE">2013-03-08 00:00:00 -0500</Attribute>
               23  <Attribute name="Postal Code">10001</Attribute>
               24  <Attribute name="Users.Update Date">2013-04-23 10:04:48 -0400</Attribute>
               25  <Attribute name="Employee Number">2335</Attribute>
               26  <Attribute name="USR_UDF_DEPARTMENT_NAME">Vision Corporation</Attribute>
               27  <Attribute name="Users.Last Name">testfuturedate102</Attribute>
               28  <Attribute name="Users.End Date">4712-12-31 00:00:00 -0500</Attribute>
               29  <Attribute name="Hire Date">2013-02-28 00:00:00 -0500</Attribute>
               30  <Attribute name="USR_UDF_PERSON_ID">31967</Attribute>
               31  <Attribute name="USR_UDF_IS_MANAGER">N</Attribute>
               32  <Attribute name="Users.Creation Date">2013-04-23 10:04:48 -0400</Attribute>
               33  <Attribute name="Street">500 Madison Ave</Attribute>
               34  <Attribute name="Users.User ID">31967</Attribute>
               35  <Attribute name="USR_UDF_CITY">New York</Attribute>
               36  <Attribute name="Users.Lock User">0</Attribute>
               37  <Attribute name="Users.Updated By Login" key="4">OIMINTERNAL</Attribute>
               38  <Attribute name="Users.Created By Login" key="4">OIMINTERNAL</Attribute>
               39  <Attribute name="Users.Login Attempts Counter">0</Attribute>
               40  <Attribute name="Organizations.Organization Name" key="4">Integra</Attribute>
               41  </UserInfo>
               42  </UserProfileSnapshot>'
               43  );
               
              1 row inserted
               
              SQL> 
              SQL> select x.*
                2  from table1 t
                3     , xmltable(
                4         '/UserProfileSnapshot/UserInfo'
                5         passing xmltype(t.column1)
                6         columns userdisplay_name varchar2(30) path 'Attribute[@name="Users.Display Name"]'
                7               , user_role        varchar2(30) path 'Attribute[@name="Users.Role"]'
                8       ) x ;
               
              USERDISPLAY_NAME               USER_ROLE
              ------------------------------ ------------------------------
              testfuturedate102, Mr. user    Employee
               
              • 4. Re: Query required for xml processing.
                986217
                thanks ..

                I was not able to get the data so I modified the query as extractvalue( xmltype (replace(snapshot, Chr(39), Chr(34))), '/UserProfileSnapshot/UserInfo/Attribute[@name="Users.User ID"]' ) as USER_ID