OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » Query XML

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query XML [message #424124]

Wed, 30 September 2009 05:18

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

Report message to a moderator

Re: Query XML [message #424145 is a reply to message #424124]

Wed, 30 September 2009 05:59

ThomasG
Messages: 3211
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>

Report message to a moderator

Re: Query XML [message #424153 is a reply to message #424124]

Wed, 30 September 2009 06:18

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Previous Topic:	JDev Won't Start
Next Topic:	Rename LOV data in OAF page coming from database messages

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 23:51:23 CDT 2024