| Author |
Message
|
| adamcadaver |
 Posted: Wed Nov 27, 2013 9:04 am Post subject: SELECT return value filtered using attribute value? |
 |
|
Newbie
Joined: 27 Nov 2013
Posts: 4
|
I am trying to return the value of a element in an example message based on attribute value of the parent
the structure of the XML message is:
| Code: |
<transaction>
<operation>
<column name="A">
<item>Desired Value</item>
</column>
<column name="B">
<item>Other Value</item>
</column>
</operation>
</transaction> |
In XPath this would be done in with the following query:
| Code: |
| /transaction/operation/column[@name='A']/item/test() |
But In ESQL I can not get it to work. I'm thinking it should be something akin to:
| Code: |
DECLARE variable ROW;
SET variable[] = (
SELECT
FIELDVALUE(C.column.item)
FROM
InputRoot.XMLNSC.transaction.operation as C
WHERE
FIELDVALUE(C.column.(XML.Attribute)name) = "A" |
Help would be much appreciated!!!
Last edited by adamcadaver on Wed Nov 27, 2013 11:58 am; edited 3 times in total |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Nov 27, 2013 9:28 am Post subject: Re: SELECT return value filtered using attribute value? |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| adamcadaver wrote: |
| Help would be much appreciated!!! |
When you tried it, what happened?
What version of WMB are you using?
If you're using (as you should) the XMLNSC domain and not the depreciated XML one, it's XMLNSC.Attribute you should be casting as.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| adamcadaver |
| Posted: Wed Nov 27, 2013 11:57 am Post subject: |
|
|
Newbie
Joined: 27 Nov 2013
Posts: 4
|
I tried to assign the return value of the SELECT function to a row variable
| Code: |
DECLARE variable ROW;
SET variable[] = ... |
the flow deployed but the variable was not assigned a value when executed.
I am using WMB 7.0.0.6
My apologies indeed I am using the XMLNSC domain and I have updated my original post to reflect this.
Thank you for your reply Vitor! |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Nov 27, 2013 12:51 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| adamcadaver wrote: |
| the flow deployed but the variable was not assigned a value when executed. |
Best advice in this sort of situation is to take a user trace (which is distinct from using a Trace node) and see exactly what's going on.
A Trace node might be valuable to ensure that the message tree (including the presence or absence of attributes) is exactly what you think it is.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Nov 27, 2013 2:34 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
You should not use XML.Attribute ( or indeed XML.anything ) when dealing with a tree owned by XMLNSC. This might work better:
| Code: |
DECLARE variable ROW;
SET variable[] = (
SELECT
FIELDVALUE(C.column.item)
FROM
InputRoot.XMLNSC.transaction.operation as C
WHERE
FIELDVALUE(C.column.(XMLNSC.Attribute)name) = "A" |
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| adamcadaver |
| Posted: Thu Nov 28, 2013 7:10 am Post subject: |
|
|
Newbie
Joined: 27 Nov 2013
Posts: 4
|
Unfortunately does that not work either.
Is it possible to use a XML attribute this way in a WHERE statement in ESQL? |
|
| Back to top |
|
|
| Simbu |
| Posted: Thu Nov 28, 2013 8:03 am Post subject: |
|
|
Master
Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India
|
If I'm able to get your code work with small changes then you should also get the same result.
1) It should be like SET variable.something[] =
2)FIELDVALUE(C.column.(XMLNSC.Attribute)name) = 'A' |
|
| Back to top |
|
|
| adamcadaver |
| Posted: Fri Nov 29, 2013 1:38 am Post subject: |
|
|
Newbie
Joined: 27 Nov 2013
Posts: 4
|
Yep solved. This issue I was facing was that the 'desired value' was CDATA. I have worked around this as follows:
DECLARE reftemp ROW;
DECLARE tempValue CHAR;
SET reftemp = THE (
SELECT C.item
FROM InputRoot.XMLNSC.transaction.operation.column[] AS C
WHERE C.(XMLNSC.Attribute)name = 'A'
);
SET tempValue = reftemp.item;
Thank you so much for your help guys!
- Adam |
|
| Back to top |
|
|
| Simbu |
| Posted: Fri Nov 29, 2013 2:14 am Post subject: |
|
|
Master
Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India
|
Please use code tag when you are posting code.
| Quote: |
| Use a SELECT statement to return a scalar value by including both the THE and ITEM keywords |
| Code: |
DECLARE tempValue CHAR;
SET tempValue = THE (
SELECT ITEM C.item
FROM InputRoot.XMLNSC.transaction.operation.column[] AS C
WHERE C.(XMLNSC.Attribute)name = 'A'
); |
|
|
| Back to top |
|
|
|
|
