Home » Developer & Programmer » JDeveloper, Java & XML » Query XML
Query XML [message #424124] Wed, 30 September 2009 05:18 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,

Below is my xml code:-


<UserPref>
  <Preference>
    <UserAttribute>
      <AttributeValueTx>81345</AttributeValueTx>
      <ABCApplicationCd/>
      <AttributeNameTx>PREF_GRP_ID</AttributeNameTx>
    </UserAttribute>
  </Preference>
  <Preference>
    <UserAttribute>
      <AttributeValueTx>36EDD640A35C92F0852568F6005D</AttributeValueTx>
      <ABCApplicationCd/>
      <AttributeNameTx>I_WATCH_LIST</AttributeNameTx>
    </UserAttribute>
    <UserAttribute>
      <AttributeValueTx>5989444CA83F5CAF852568160079AFE9</AttributeValueTx>
      <ABCApplicationCd/>
      <AttributeNameTx>I_WATCH_LIST</AttributeNameTx>
    </UserAttribute>     
  </Preference>
  <Preference>
    <UserAttribute>
      <AttributeValueTx>IM_REAL_ESTATE</AttributeValueTx>
      <ABCApplicationCd/>
      <AttributeNameTx>PREF_INDUSTRY_TYPE</AttributeNameTx>
    </UserAttribute>
  </Preference>
  <Preference>
    <UserAttribute>
      <AttributeValueTx>175</AttributeValueTx>
      <ABCApplicationCd/>
      <AttributeNameTx>C_WATCH_LIST</AttributeNameTx>
    </UserAttribute>
    <UserAttribute>
      <AttributeValueTx>393</AttributeValueTx>
      <ABCApplicationCd/>
      <AttributeNameTx>C_WATCH_LIST</AttributeNameTx>
    </UserAttribute>   
  </Preference>
</UserPref>


I wanted to fetch the AttributeValueTx values of AttributeNameTx= C_WATCH_LIST

I created my sql as below


SELECT EXTRACT
           (pasu.system_user_attributes_xt,
            '/UserPref/Preference/UserAttribute/AttributeValueTx '
           ).getstringval () val
  FROM party_owner.system_user_pasu pasu
 WHERE EXISTSNODE
          (pasu.system_user_attributes_xt,
           '/UserPref/Preference/UserAttribute[AttributeNameTx = "C_WATCH_LIST"]'
          ) = 1


Above sql returns me all the AttributeValueTx as below


<AttributeValueTx>81345</AttributeValueTx><AttributeValueTx>36EDD640A35C92F0852568F6005D</AttributeValueTx> <AttributeValueTx>5989444CA83F5CAF852568160079AFE9</AttributeValueTx><AttributeValueTx>IM_REAL_ESTATE</AttributeValueTx><AttributeValueTx>175</AttributeValueTx><AttributeValueTx>393</AttributeValueTx>


I am only interested in attributes value belonging to AttributeNameTx = "C_WATCH_LIST"]
i,e


<AttributeValueTx>175</AttributeValueTx><AttributeValueTx>393</AttributeValueTx>


Anyways I can get it?

Thanks
Nav
Re: Query XML [message #424145 is a reply to message #424124] Wed, 30 September 2009 05:59 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can use the "child" XPath axis.

You select all UserAttribute with AttributeNameTx "C_WATCH_LIST", and then all their children AttributeValueTx:

SQL> SELECT EXTRACT
  2             (col,
  3              '/UserPref/Preference/UserAttribute[AttributeNameTx =
  4              "C_WATCH_LIST"]/child::AttributeValueTx'
  5             ).getstringval () val
  6    FROM test_xml
  7  ;

VAL
--------------------------------------------

<AttributeValueTx>175</AttributeValueTx><AttributeValueTx>393</AttributeValueTx>
SQL>
Re: Query XML [message #424153 is a reply to message #424124] Wed, 30 September 2009 06:18 Go to previous message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Depending on the final usage you want to make, here another way to do it:
SQL> with
  2    data as (
  3      select xmltype('<UserPref>
  4    <Preference>
  5      <UserAttribute>
  6        <AttributeValueTx>81345</AttributeValueTx>
  7        <ABCApplicationCd/>
  8        <AttributeNameTx>PREF_GRP_ID</AttributeNameTx>
  9      </UserAttribute>
 10    </Preference>
 11    <Preference>
 12      <UserAttribute>
 13        <AttributeValueTx>36EDD640A35C92F0852568F6005D</AttributeValueTx>
 14        <ABCApplicationCd/>
 15        <AttributeNameTx>I_WATCH_LIST</AttributeNameTx>
 16      </UserAttribute>
 17      <UserAttribute>
 18        <AttributeValueTx>5989444CA83F5CAF852568160079AFE9</AttributeValueTx>
 19        <ABCApplicationCd/>
 20        <AttributeNameTx>I_WATCH_LIST</AttributeNameTx>
 21      </UserAttribute>     
 22    </Preference>
 23    <Preference>
 24      <UserAttribute>
 25        <AttributeValueTx>IM_REAL_ESTATE</AttributeValueTx>
 26        <ABCApplicationCd/>
 27        <AttributeNameTx>PREF_INDUSTRY_TYPE</AttributeNameTx>
 28      </UserAttribute>
 29    </Preference>
 30    <Preference>
 31      <UserAttribute>
 32        <AttributeValueTx>175</AttributeValueTx>
 33        <ABCApplicationCd/>
 34        <AttributeNameTx>C_WATCH_LIST</AttributeNameTx>
 35      </UserAttribute>
 36      <UserAttribute>
 37        <AttributeValueTx>393</AttributeValueTx>
 38        <ABCApplicationCd/>
 39        <AttributeNameTx>C_WATCH_LIST</AttributeNameTx>
 40      </UserAttribute>   
 41    </Preference>
 42  </UserPref>') val
 43      from dual
 44    )
 45  select extractvalue(value(t),'/UserAttribute/AttributeValueTx') val
 46  from data,
 47       table(xmlsequence(extract(data.val, '/UserPref/Preference/UserAttribute'))) t
 48  where extractvalue(value(t),'/UserAttribute/AttributeNameTx') = 'C_WATCH_LIST'
 49  /
VAL
------------
175
393

2 rows selected.
Previous Topic: JDev Won't Start
Next Topic: Rename LOV data in OAF page coming from database messages
Goto Forum:
  


Current Time: Sat Oct 01 11:02:19 CDT 2016

Total time taken to generate the page: 0.13029 seconds