| Author |
Message
|
| aks |
 Posted: Mon Aug 16, 2004 4:52 pm Post subject: Help with SELECT |
 |
|
Voyager
Joined: 19 Jul 2002
Posts: 84
|
I have xml that looks like this
<Field1>
<Value>1000</Value>
</Field1>
<Field2>
<Value>2000</Value>
</Field2>
<Field3>
<Value>3000</Value>
</Field3>
and so on etc
There can be many of these blocks (all optional though) and all begin with "Field" then an integer.
I want to sum all the Value elements with a select statement.
Not sure what to put in the FROM clause, or would I have to use a reference instead of a SELECT?
Thanks
Alan |
|
| Back to top |
|
 |
| Nizam |
| Posted: Mon Aug 16, 2004 6:26 pm Post subject: |
|
|
Disciple
Joined: 10 Feb 2004
Posts: 160
|
aks,
I have never used select myself to do something what you r doing. But I want to give it a try.
Width = 1;
sum = 0;
path = InputRoot.XML.TOP_FILED.*[Width];
while(Lastmove)
do
if(substring(path from 1 to 5) = 'field') then
sum = sum + path.value
end if;
move Path NextSibling;
end while;
This is just an idea...
Let me know if this solves your purpose..... |
|
| Back to top |
|
|
| Missam |
| Posted: Mon Aug 16, 2004 7:16 pm Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
| Quote: |
| There can be many of these blocks (all optional though) and all begin with "Field" then an integer |
what do you mean by all optional here .is your requirement can be some thing like this
| Quote: |
<Field1>
<Value>1000</Value>
</Field1>
<Field3>
<Value>2000</Value>
</Field3>
<Field6>
<Value>3000</Value>
</Field6>
|
with optional filed2 ,4 and 5 |
|
| Back to top |
|
|
| aks |
| Posted: Mon Aug 16, 2004 9:19 pm Post subject: |
|
|
Voyager
Joined: 19 Jul 2002
Posts: 84
|
Sorry - optional means that the numbers will still be sequential, ie if there are 3 present, it wil have Field1, Field2 and Field3, but there can be anywhere from 0 onwards
Alan |
|
| Back to top |
|
|
| TDS_tds_tds |
| Posted: Thu Aug 19, 2004 12:19 pm Post subject: SOLVED |
|
|
Novice
Joined: 31 Jul 2003
Posts: 16
|
hey AKS, I have the solution for u.
but the xml on ur 1st post does not have a root element.... which makes it invalid xml data and mqsi will not parse it. ( i assume you forgot to post the root element). i just want to make sure you are aware of it.
so i added a root element "Test" to your xml .... here it is
| Quote: |
| <Test><Field1><Value>1000</Value></Field1><Field2><Value>2000</Value></Field2><Field3><Value>3000</Value></Field3></Test> |
And the code to calculate the sum is :
| Code: |
SET whatever = (SELECT SUM(CAST(myRef."Value" AS INTEGER)) FROM InputRoot.XML.Test.*[] AS myRef);
|
OR
| Code: |
SET whatever = (SELECT SUM(CAST(myRef.*[LAST] AS INTEGER)) FROM InputRoot.XML.Test.*[] AS myRef);
|
('whatever' in the above code refers to any variable or output tree element value or anything u want )
let me know if everything works cool for u. |
|
| Back to top |
|
|
|
|
