ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportSELECT statement to find an XML tag in a dynamic structure

Post new topicReply to topic
SELECT statement to find an XML tag in a dynamic structure View previous topic :: View next topic
Author Message
souciance
PostPosted: Wed Sep 27, 2017 12:51 am Post subject: SELECT statement to find an XML tag in a dynamic structure Reply with quote

Acolyte

Joined: 29 Jun 2010
Posts: 51

Hello,

I have an XML structure that looks like this:

<Root>
<Structure>
<Instances>
<Instance1>
<Instance11>
<field value=2/>
<instance1>
<instances11>
<instances111>
<field value=2/>

Essentially I am after all the "field" tags. However the field tag can occur in dynamically nested structure. I can be present two levels two, three levels deep or n levels deep.

XPath would be great for this but is this possible to use via SELECT statements?

Souciance
Back to top
View user's profile Send private message
mqjeff
PostPosted: Wed Sep 27, 2017 4:22 am Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17446

You can use FIELDNAME in an ESQL Select, afair.

You can at least use XPATH from a JCN, and I thought there was maybe some new function in IIBv9 or 10 that let you run XPATH from esql... but that's just a very vague memory that might have been a dream (or nightmare)
_________________
Read, Think, Try, Repeat
Back to top
View user's profile Send private message
souciance
PostPosted: Wed Sep 27, 2017 6:32 am Post subject: Reply with quote

Acolyte

Joined: 29 Jun 2010
Posts: 51

True, but if I don't know the path in advance how can the SELECT work even if I state the FIELDNAME? The field itself could be 1 layer deep or 10 layers deep? I am not sure but don't think the SELECT traverses recursively down each structure, or does it?

Yeah an ESQL xpath method would be great!

I have resorted to using the JCN stuff.

However, its a bit of pain there too as getAllElementsByPath() is deprecated and evaluateXPath does not seem to play unless I add namespaces and I have tried various ways but it still returns empty..

May be forced to use the deprecated method even if its ugly.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Wed Sep 27, 2017 7:39 am Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17446

souciance wrote:
True, but if I don't know the path in advance how can the SELECT work even if I state the FIELDNAME? The field itself could be 1 layer deep or 10 layers deep? I am not sure but don't think the SELECT traverses recursively down each structure, or does it?


Don't see how "SELECT A.*" would work otherwise, yes?

Not that using A.* is a good idea... because it does traverse the whole tree.
_________________
Read, Think, Try, Repeat
Back to top
View user's profile Send private message
souciance
PostPosted: Sun Oct 01, 2017 8:56 am Post subject: Reply with quote

Acolyte

Joined: 29 Jun 2010
Posts: 51

Yeah after some troubleshoot with the java API for evaluateXPath I managed to get it to work.

Another nice addition would be if you can automagically get the resultset from an evaluateXPath and add it directly to some field under local environment. Right now you need a list of MbElement and then you have traverse that list etc.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Sun Oct 01, 2017 3:13 pm Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17446

This is something I wrote a long time ago:
Code:
   public static String evaluateXPath(MbElement[] msgTree, String xPathExpression, MbElement[] resultTree)
   {
      String result = null;
      MbElement newElem = null;
      Object returnVal = null;
      try {
         returnVal = msgTree[0].evaluateXPath(xPathExpression);
         result = "success";
         if ((returnVal instanceof Boolean) || (returnVal instanceof Double)) {
               newElem = resultTree[0].createElementAsFirstChild(MbElement.TYPE_NAME_VALUE ,"XpathResultTree",returnVal.toString());
               result += " boolean or long result";
         } else if (returnVal instanceof String) {
            newElem = resultTree[0].createElementAsFirstChild(MbElement.TYPE_NAME_VALUE ,"XpathResultTree",returnVal);
            result += " String result";
         } else if (returnVal instanceof MbElement[]) {
            resultTree[0] = ((MbElement[])returnVal)[0];
            result += " MbElement[] result";
         } else if (returnVal instanceof List) {
            for (Iterator myIter = ((List)returnVal).iterator(); myIter.hasNext();) {
               newElem = (MbElement) myIter.next();
               resultTree[0].addAsLastChild(newElem);
               result += " MbElement list result";
            }
         }
      } catch (MbException e) {
         StringWriter sw = new StringWriter();
         PrintWriter pw = new PrintWriter(sw);
         e.printStackTrace(pw);
         result = sw.toString();
      }
      return result;
   };


You would call it from ESQL like
Code:
      Declare tree REFERENCE to OutputRoot.XMLNSC;
      Set OutputRoot.XMLNSC.Result.Return = '';
      declare result reference to OutputRoot.XMLNSC.Result.Return;
      set OutputRoot.XMLNSC.Result.Value = evaluateXPath(tree,'1 = 1',result );

CREATE PROCEDURE evaluateXPath(INOUT tree REFERENCE, IN path CHARACTER, INOUT result REFERENCE)
RETURNS CHARACTER
LANGUAGE JAVA
EXTERNAL NAME "esqlXPathHelper.evaluateXPath";


Obviously the "1=1" is a dummy xpath expression.
_________________
Read, Think, Try, Repeat
Back to top
View user's profile Send private message
souciance
PostPosted: Sun Oct 01, 2017 10:50 pm Post subject: Reply with quote

Acolyte

Joined: 29 Jun 2010
Posts: 51

That's pretty cool, I'll have to save that for future reference. I got it to work like this (excluding error handling code):
Code:

MbNamespaceBindings ns = new MbNamespaceBindings();
         ns.setDefaultNamespace("http://mycompany.org/XXX/XXXX/2.0");
         MbXPath xp = new MbXPath("//elementIamAfter", ns);      
         MbElement root = inMessage.getRootElement();
         MbElement xmlnsc = root.getFirstElementByPath("XMLNSC");         
         List<MbElement> nodeset =  (List<MbElement>)xmlnsc.evaluateXPath(xp);
         MbMessage env = outAssembly.getLocalEnvironment();
         env.getRootElement().createElementAsFirstChild(MbElement.TYPE_NAME, "elementsOfWhatIwant", null);
         for (int x = 0; x<nodeset.size(); x++) {
            MbElement idref = nodeset.get(x);            
            String idrefVal = idref.getFirstElementByPath("idref").getValueAsString();            
            env.getRootElement().getFirstChild().createElementAsFirstChild(MbElement.TYPE_NAME_VALUE, "ElementNameOfWhatIamAfter", idrefVal);            
         }
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportSELECT statement to find an XML tag in a dynamic structure
Jump to:



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.