| Author |
Message
|
| souciance |
 Posted: Wed Sep 27, 2017 12:51 am Post subject: SELECT statement to find an XML tag in a dynamic structure |
 |
|
Disciple
Joined: 29 Jun 2010
Posts: 169
|
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 |
|
 |
| mqjeff |
| Posted: Wed Sep 27, 2017 4:22 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
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)
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| souciance |
| Posted: Wed Sep 27, 2017 6:32 am Post subject: |
|
|
Disciple
Joined: 29 Jun 2010
Posts: 169
|
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 |
|
|
| mqjeff |
| Posted: Wed Sep 27, 2017 7:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| 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.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| souciance |
| Posted: Sun Oct 01, 2017 8:56 am Post subject: |
|
|
Disciple
Joined: 29 Jun 2010
Posts: 169
|
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 |
|
|
| mqjeff |
| Posted: Sun Oct 01, 2017 3:13 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
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.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| souciance |
| Posted: Sun Oct 01, 2017 10:50 pm Post subject: |
|
|
Disciple
Joined: 29 Jun 2010
Posts: 169
|
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 |
|
|
|
|
