By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

XML Query Help

P: 3
Hello,

The problem is my query is not extracting the correct xml data from the database :
Is there something which I am missing??
ANy help would be appriciated.
Thanks,
Sam.

THis is the query which I am using to extract :

select ds_key_id, ds_ver_num,
pkg_report_common.get_keyword(ds.ds_cond_xml.extra ct('//condition/LHS/keyword@id').getStringVal()) as keyword_id,
ds.ds_cond_xml.extract('//condition/LHS/keyword@id').getStringVal() as key_id,
ds.ds_cond_xml.extract('//condition/operator/text()').getStringVal() as operator_text,
ds.ds_cond_xml.extract('//condition/RHS/value/text()').getStringVal() as keyword_val
from dataset_version ds
=====================================

OUT PUT ::::
------------------------------------------------
DS_KEY_ID DS_VER_NUM KEYWORD_ID KEY_ID OPERATOR_TEXT KEYWORD_VAL
7601 7851 Total Market Cap 14 GT 5000000000
7601 353051 Country 2 IN 189751ANGL
152951 158251 [NULL] [NULL] [NULL] [NULL]
153001 158301 Category 5 IN 6
153051 158351 Category 5 NOTIN 6
157701 163001 Currency 1 EQ ASTL




=============================
XML Data:

DS_KEY_ID : 7601
DS_VER_NUM : 353051
---------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<conditions>
<logical-operator type="AND">
<condition id="2" name="2" setid="1" setname="ConditionSet1">
<LHS><keyword id="2"></keyword></LHS>
<RHS><value source="REFSVC" type="LIST_ITEM">USA</value></RHS>
<operator>EQ</operator>
</condition>
<condition id="1" name="1" setid="1" setname="ConditionSet1">
<LHS><keyword id="1"></keyword></LHS>
<RHS><value source="REFSVC" type="LIST_ITEM">USA</value></RHS>
<operator>EQ</operator>
</condition>
</logical-operator>
</conditions>
=======================================
DS_KEY_ID : 7601
DS_VER_NUM : 7851
-----------------
<?xml version="1.0" encoding="UTF-8"?>
<conditions>
<logical-operator type="AND">
<condition id="2" name="2" setid="1" setname="ConditionSet1">
<LHS><keyword id="2"></keyword></LHS><
RHS><value source="REFSVC" type="LIST_ITEM">USA</value></RHS>
<operator>NE</operator>
</condition>
<condition id="1" name="1" setid="1" setname="ConditionSet1">
<LHS><keyword id="1"></keyword></LHS>
<RHS><value source="REFSVC" type="LIST_ITEM">USA</value></RHS>
<operator>NE</operator>
</condition>
</logical-operator>
</conditions>
==========================================
DS_KEY_ID : 152951
DS_VER_NUM : 158251
-----------------

NULL

=========================
DS_KEY_ID : 153001
DS_VER_NUM : 158301
-----------------
<?xml version="1.0" encoding="UTF-8"?>
<conditions><logical-operator type="OR">
<logical-operator type="AND">
<condition id="2" name="2" setid="1" setname="ConditionSet1">
<LHS><keyword id="2"></keyword></LHS>
<RHS><value Source="REFSVC" Type="LIST_ITEM">USA</value></RHS>
<operator>NE</operator>
</condition>
<condition id="4" name="4" setid="1" setname="ConditionSet1">
<LHS><keyword id="4"></keyword></LHS>
<RHS><value source="REFSVC" Type="LIST_ITEM">CS</value></RHS>
<operator>EQ</operator>
</condition>
</logical-operator>
<condition id="1" name="1" setid="2" setname="ConditionSet2">
<LHS><keyword id="1"></keyword></LHS>
<RHS><value source="LIST" Type="LIST">98751</value></RHS>
<operator>IN</operator>
</condition>
</logical-operator>
</conditions>
=========================
DS_KEY_ID : 153051
DS_VER_NUM : 158351
-----------------
<?xml version="1.0" encoding="UTF-8"?>
<conditions>
<logical-operator type="OR">
<logical-operator type="AND">
<condition id="2" name="2" setid="1" setname="ConditionSet1">
<LHS><keyword id="2"></keyword></LHS>
<RHS><value source="REFSVC" type="LIST_ITEM">USA</value>
<value source="REFSVC" type="LIST_ITEM">CANA</value></RHS>
<operator>IN</operator>
</condition>
<condition id="4" name="4" setid="1" setname="ConditionSet1">
<LHS><keyword id="4"></keyword></LHS>
<RHS><value source="LIST" Type="LIST">98801</value></RHS>
<operator>IN</operator>
</condition>
</logical-operator>
<condition id="2" name="2" setid="2" setname="ConditionSet2">
<LHS><keyword id="2"></keyword></LHS>
<RHS><value source="LIST" type="LIST">98851</value></RHS>
<operator>IN</operator>
</condition>
<condition id="1" name="1" setid="3" setname="ConditionSet3">
<LHS><keyword id="1"></keyword></LHS>
<RHS><value source="LIST" type="LIST">98901</value></RHS>
<operator>IN</operator>
</condition>
</logical-operator>
</conditions>
=========================
DS_KEY_ID : 157701
DS_VER_NUM : 163001
-----------------
<?xml version="1.0" encoding="UTF-8"?>
<conditions>
<condition id="5" name="5" setid="1" setname="ConditionSet1">
<LHS><keyword id="5"></keyword></LHS>
<RHS><value source="KEYWORD" type="LIST_ITEM">6</value></RHS>
<operator>IN</operator>
</condition>
</conditions>
Jul 12 '07 #1
Share this Question
Share on Google+
5 Replies


jkmyoung
Expert 100+
P: 2,057
What is it exactly that you are trying to get back from the data? eg What is the difference compared to your current output?
Jul 12 '07 #2

P: 3
What is it exactly that you are trying to get back from the data? eg What is the difference compared to your current output?
Hi,
I have 2 questions
1. The query whatever I have written is it correct (is it correctly extracting the attributes).
2. If my query is correct, then is output is correct ?? (but I see the output is not correct compared to the XML data which i have displayed)

I hope these question clarifies your questions.

Thanks,
SAM
Jul 13 '07 #3

jkmyoung
Expert 100+
P: 2,057
1. Problem 1, you have multiple conditions for each file. Your code seems to only generate the first row in each.

How are you generating these other source files? eg. Are you passing in queries with the 2 parameters beforehand?
Jul 13 '07 #4

P: 3
1. Problem 1, you have multiple conditions for each file. Your code seems to only generate the first row in each.

How are you generating these other source files? eg. Are you passing in queries with the 2 parameters beforehand?
Hello,

1. We need to add couple of more conditions to the query, right now just testing with few conditions.
2. we are not generating the source files. The XML data will be a XML column in the dataset_version table.
Problems:
1. query is not picking up the right XML data/column from the table, why it is not picking up the correct XML values, something to do with oracle version or is it rapid sql problem which I am using as tool for my query.
2. query itself if wrong.
Jul 13 '07 #5

jkmyoung
Expert 100+
P: 2,057
The only thing that comes to mind is the way you are generating the first 2 columns as opposed to the way you are generating the other columns.

Are you generating the 2 tables seperately and then simply linking them row 1 to row 1, row2 to row2? If so, this won't work and will give invalid data like the results you are receiving, since there are more rows of the following data (from condition nodes) then the first 2.

I don't know how you're generating this data set; perhaps the code there is invalid.
Jul 16 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.