| oracle 11g along with XML [message #618625] |
Mon, 14 July 2014 07:13  |
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
|
Member |
|
|
create or replace
PROCEDURE TEST_MANAGE_FILTER_1
(
p_request_id IN NUMBER )
AS
p_net_call_sign VARCHAR(50);
p_promo_types VARCHAR (200);
p_from_date VARCHAR(20);
BEGIN
SELECT filter_xml INTO l_filter_set FROM TABLE A WHERE subscriber_request_id = p_request_id ;
SELECT extractvalue(l_filter_set,'/DataSubscriber/BusinessComponent[@Name="Undefined"]/FilterGroup/Filter[@Name="Week"]/@DisplayValue')
INTO p_net_call_sign FROM dual;
dbms_output.put_line('p_week='||p_net_call_sign);
END
/
TEST XML
==========
<DataSubscriber Name="PromoUsage">
<BusinessComponent Name="Undefined">
<FilterGroup Name="SelectCriteria">
<Filter Name="Pro_T" Id="1796" DisplayValue="Billboard">BILLB007</Filter>
<Filter Name="Pro_T" Id="1796" DisplayValue="RFI Overlay">RFI_O053</Filter>
<Filter Name="Pro_T" Id="1796" DisplayValue="Paid Programming">PAID_008</Filter>
<Filter Name="Week" Id="1797" DisplayValue="***ALL VALUES***">***ALL VALUES***</Filter>
<Filter Name="Network" Id="1795" DisplayValue="AMC">E232F9BA-D997-DEB2-E040-1EAC15A531A1</Filter>
<Filter Name="Network" Id="1795" DisplayValue="WE">E232F9BA-D998-DEB2-E040-1EAC15A531A1</Filter>
</FilterGroup>
</BusinessComponent>
<DisplayOptions>
<DisplayOption Name="subtitles" Id="819" DisplayValue="Checked">Y</DisplayOption>
<DisplayOption Name="ConsumerFormat" Id="820" DisplayValue="PDF">Type=SSRS;Path=PromoUsage.rdl;Format=pdf;Id=137</DisplayOption>
<DisplayOption Name="FilterPage" Id="821" DisplayValue="Display as first page">T</DisplayOption>
</DisplayOptions>
</DataSubscriber>
==============================
Issue: I have to read the values of Pro_t, week and network in three separate statements but then I need to store the values of pro_t, network and week in a comma separated strings i.e.
for Promo_T the values as Billboard, RFI OVerlay, Paid Programming
for Week ****ALL_VALUES***
for Network AMC,WE
I need to use these three strings separately as a input parameter to a string where the select statement will be executed corresponding to these values as ('Billboard, RFI OVerlay, Paid Programming')
('****ALL_VALUES***')
('AMC,WE')
How to read the values from XML for these three different parameters and then store them separately in a variable so that they can be used separately
|
|
|
|
|
|
|
|
|
|
| Re: oracle 11g along with XML [message #618715 is a reply to message #618663] |
Tue, 15 July 2014 00:53  |
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
|
Member |
|
|
CREATE TABLE EMPLOYEES_ASH
(
id NUMBER,
data XMLTYPE
);
INSERT INTO EMPLOYEES_ASH
VALUES (1, xmltype ('<Employees>
<Employee emplid="1111" type="admin">
<firstname>John</firstname>
<lastname>Watson</lastname>
<age>30</age>
<email>johnwatson@sh.com</email>
</Employee>
<Employee emplid="2222" type="admin">
<firstname>Sherlock</firstname>
<lastname>Homes</lastname>
<age>32</age>
<email>sherlock@sh.com</email>
</Employee>
<Employee emplid="3333" type="user">
<firstname>Jim</firstname>
<lastname>Moriarty</lastname>
<age>52</age>
<email>jim@sh.com</email>
</Employee>
<Employee emplid="4444" type="user">
<firstname>Mycroft</firstname>
<lastname>Holmes</lastname>
<age>41</age>
<email>mycroft@sh.com</email>
</Employee>
</Employees>'));
SELECT t.id, x.*
FROM employees_ash t,
XMLTABLE ('/Employees/Employee'
PASSING t.data
COLUMNS firstname VARCHAR2(30) PATH 'firstname',
lastname VARCHAR2(30) PATH 'lastname',
age NUMBER PATH 'age',
email VARCHAR2(20) PATH 'email') x
WHERE t.id = 1;
I used the above example. But in my case I need to read from a XML file which is inside a table column. It is not a independent xml which I can insert into a table.
Also I need to store the values in a kind of a comma separated string for three different variables which are promo_t, week and Network. Hope it will clarify my concerns!
|
|
|
|
|
|
| Re: oracle 11g along with XML [message #618784 is a reply to message #618715] |
Tue, 15 July 2014 09:35 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ashish_sun123 wrote on Tue, 15 July 2014 01:53But in my case I need to read from a XML file which is inside a table column. It is not a independent xml which I can insert into a table.
I have no clue what you mean. In example you posted t.data is a column in a table and that column stores XML document - same as in your original post.
SY.
|
|
|
|
| Re: oracle 11g along with XML [message #618786 is a reply to message #618784] |
Tue, 15 July 2014 10:32 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 15 July 2014 12:28Michel Cadot wrote on Mon, 14 July 2014 15:21
John Watson wrote on Thu, 16 June 2011 13:09What about saying "thank you"?
This means you have to review your previous topics.
For newbies who might not understand what Michel is trying to say :
This forum, (unlike other forums having point system to accredite the helpers with legends) consists of volunteers who doesn't care about the legend/rank. Giving a feedback is not only necessary to acknowledge the suggestion, but, it would complete the thread as well as it makes sure that the provided solution/suggestion really works practically.
Also, as Michel mentioned, reviewing previous topics is important since most of the posters who are amateurs tend to post a new topic which ultimately relates to the previous posts. I do understand that it might not be an easy task for newbies to understand the difference in the subsequent questions, considering the different levels of expertise of folks participating in the forums.
So, per whatever I kept blabbering above, all it needs is to come back with a feedback that constitutes a working test case which shows what you have implemented based on the suggestions.
Good luck!
NOTE : This is a off topic message. It bears no relevance to the technical aspects of this thread. Its only me who is responsible for the thoughts shared and folks who don't agree could PM me with their deferred thoughts and further suggestions. Sorry for an off topic post.
Regards,
Lalit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
