Author |
Message
|
madrox |
Posted: Tue Jan 12, 2021 1:29 pm Post subject: Selecting XML with Namespaces from Environment |
|
|
![](images/avatars/367756885526ed8ee71d3.jpg) Acolyte
Joined: 11 Mar 2015 Posts: 71
|
Hey everyone
I a repeating xml stored in the environment the structure below
Code: |
<?xml version="1.0" encoding="utf-8"?>
<EMPLOYEE xmlns="http://sample/iibadmin" xmlns:ibmSchExtn="http://www.ibm.com/schema/extensions" xsi:schemaLocation="http://sample/iibadmin schema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<EMPNO>123456</EMPNO>
<FIRSTNME>Test1</FIRSTNME>
<MIDINIT>P</MIDINIT>
<LASTNAME>Last1</LASTNAME>
<WORKDEPT>IT</WORKDEPT>
<PHONENO>123456789</PHONENO>
<HIREDATE>2020-08-02</HIREDATE>
<JOB>ESB</JOB>
<EDLEVEL>16</EDLEVEL>
<SEX>M</SEX>
<BIRTHDATE>1979-11-02</BIRTHDATE>
<SALARY>20000</SALARY>
<BONUS>500</BONUS>
<COMM>15000</COMM>
</EMPLOYEE> |
I am trying to do a select to find the Firstname when i pass the employee number
Code: |
DECLARE Empno CHAR;
SET Empno=TRIM( InputRoot.XMLNSC.test);
SET OutputRoot.XMLNSC.FirstName = THE(Select R.FIRSTNME FROM Environment.XMLNSC.XMLNSC.EMPLOYEE[] AS R where R.EMPNO=Empno); |
The above does not return anything. I was a bit puzzled and after a while
of debugging I took out the name spaces and used the below XML
Code: |
<?xml version="1.0" encoding="utf-8"?>
<EMPLOYEE>
<EMPNO>123456</EMPNO>
<FIRSTNME>Test1</FIRSTNME>
<MIDINIT>P</MIDINIT>
<LASTNAME>Last1</LASTNAME>
<WORKDEPT>IT</WORKDEPT>
<PHONENO>123456789</PHONENO>
<HIREDATE>2020-08-02</HIREDATE>
<JOB>ESB</JOB>
<EDLEVEL>16</EDLEVEL>
<SEX>M</SEX>
<BIRTHDATE>1979-11-02</BIRTHDATE>
<SALARY>20000</SALARY>
<BONUS>500</BONUS>
<COMM>15000</COMM>
</EMPLOYEE> |
When i did a select with the same ESQL code above I got the valid result.
My question is how do do a select if my XML has namespaces |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
timber |
Posted: Wed Jan 13, 2021 1:28 am Post subject: |
|
|
![](images/avatars/53508560955fb354bd4bd0.jpg) Grand Master
Joined: 25 Aug 2015 Posts: 1280
|
You need to declare a namespace constant for "http://sample/iibadmin" and use it in your SELECT statement. No different from using namespaces in any other ESQL path.
It might help to use a simple SET statement to confirm that you can access the EMPLOYEE element successfully, then move on to the (more complex) SELECT statement. |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
madrox |
Posted: Wed Jan 13, 2021 8:42 am Post subject: |
|
|
![](images/avatars/367756885526ed8ee71d3.jpg) Acolyte
Joined: 11 Mar 2015 Posts: 71
|
Thanks for your response. I tried with the below select and still nothing.
Code: |
DECLARE Empno CHAR;
DECLARE xmlns NAMESPACE 'http://sample/iibadmin';
SET Empno=TRIM(InputRoot.XMLNSC.test);
SET OutputRoot.XMLNSC.FirstName = THE(Select R.FIRSTNME FROM Environment.XMLNSC.XMLNSC.xmlns:EMPLOYEE[] AS R where R.EMPNO=Empno); |
|
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
Vitor |
Posted: Wed Jan 13, 2021 10:51 am Post subject: |
|
|
![](images/avatars/905340816499abc1cdfb07.jpg) Grand High Poobah
Joined: 11 Nov 2005 Posts: 26093 Location: Texas, USA
|
madrox wrote: |
Thanks for your response. I tried with the below select and still nothing. |
What happened when you tried the more simple SET statement suggested by my worthy associate?
Can you also post the code used to place the XML into the Environment tree? Looking at your "working" ESQL and the need to have XMLNSC twice, I wonder if the XMLNSC domain is set up correctly. If not, your XML is being stored as a message tree, which doesn't support namespaces and might explain this behavior.
Again, the more simple SET statement would be an illustrative test. _________________ Honesty is the best policy.
Insanity is the best defence. |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
madrox |
Posted: Wed Jan 13, 2021 1:48 pm Post subject: |
|
|
![](images/avatars/367756885526ed8ee71d3.jpg) Acolyte
Joined: 11 Mar 2015 Posts: 71
|
I did a simple SET but that did not return anything either
Code: |
DECLARE test CHAR;
DECLARE xmlns NAMESPACE 'http://sample/iibadmin';
SET test = Environment.XMLNSC.XMLNSC.xmlns:EMPLOYEE[1].FIRSTNME; |
Quote: |
Can you also post the code used to place the XML into the Environment tree? Looking at your "working" ESQL and the need to have XMLNSC twice, I wonder if the XMLNSC domain is set up correctly. If not, your XML is being stored as a message tree, which doesn't support namespaces and might explain this behavior. |
I am trying to retrieve an XML that is in a BLOB.
Code: |
CREATE LASTCHILD OF Environment DOMAIN 'XMLNSC' NAME 'XMLNSC';
CREATE LASTCHILD OF Environment.XMLNSC PARSE (xml OPTIONS FolderBitStream CCSID 1208 FORMAT 'XMLNSC');
SET s = Environment.XMLNSC.XMLNSC.xmlns:EMPLOYEE[1].FIRSTNME ; |
|
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
fjb_saper |
Posted: Wed Jan 13, 2021 7:43 pm Post subject: |
|
|
![](images/avatars/gallery/misc/2.gif) Grand High Poobah
Joined: 18 Nov 2003 Posts: 20696 Location: LI,NY
|
Have you declared xmlns as a namespace (you should only define it as the official xmlns namespace)
so
Code: |
Declare sample NAMESPACE Value 'xyz';
s = get Environment.XMLNSC.sample:EMPLOYEE[1].sample:FIRSTNAME;
|
Hope it helps ![](images/smiles/innocent.gif) _________________ MQ & Broker admin |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
madrox |
Posted: Wed Jan 13, 2021 7:53 pm Post subject: |
|
|
![](images/avatars/367756885526ed8ee71d3.jpg) Acolyte
Joined: 11 Mar 2015 Posts: 71
|
Yes I have, please see my code in the previous post. It's in the first code block |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
abhi_thri |
Posted: Thu Jan 14, 2021 1:28 am Post subject: |
|
|
![](images/avatars/3159673415d820d89ea89a.jpg) Knight
Joined: 17 Jul 2017 Posts: 516 Location: UK
|
Code: |
DECLARE Empno CHAR;
DECLARE xmlns NAMESPACE 'http://sample/iibadmin';
SET Empno=TRIM(InputRoot.XMLNSC.test);
SET OutputRoot.XMLNSC.FirstName = THE(Select ITEM R.FIRSTNME FROM Environment.XMLNSC.xmlns:EMPLOYEE[] AS R where R.EMPNO=Empno); |
hi...can you try with the above code (not sure why XMLNSC is used twice at Environment.XMLNSC.XMLNSC)...also added ITEM in there assuming you are looking for just the value.
If this still doesn't work check whether the xml tree is saved directly at 'Environment' or at a sub-tree eg:- 'Environment.Variables' etc.
If nothing works, try collecting the user trace of the flow which should show what exactlly is going on |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
timber |
Posted: Thu Jan 14, 2021 2:56 am Post subject: |
|
|
![](images/avatars/53508560955fb354bd4bd0.jpg) Grand Master
Joined: 25 Aug 2015 Posts: 1280
|
Lots of suggestions, so I think a quick summary would be useful:
1. Environment.XMLNSC.XMLNSC looks suspicious. Are you sure that this is the correct path. I always recommend using a Trace node to examine the message tree that you are trying to query.
2. DECLARE xmlns NAMESPACE ... is not good practice. The name 'xmlns' is reserved by the XML specification. ESQL will not stop you from (mis)using it like this, but I strongly recommend that you change the name of the constant to something meaningful. How about 'iib' or 'admin'.
3. The suggestion from abhi_thri about user trace is very good. All IIB developers should know how to take a user trace - the debugger only shows you what is happening, not why. |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
madrox |
Posted: Thu Jan 14, 2021 11:32 am Post subject: |
|
|
![](images/avatars/367756885526ed8ee71d3.jpg) Acolyte
Joined: 11 Mar 2015 Posts: 71
|
I changed the name of the name space, used iib instead of xmlns still same result.
Also tried the below code still no result
Code: |
Declare sample NAMESPACE Value 'xyz';
s = get Environment.XMLNSC.sample:EMPLOYEE[1].sample:FIRSTNAME; |
Code: |
( ['MQROOT' : 0x1258c3450]
(0x01000000:Folder):XMLNSC = ( ['xmlnsc' : 0x1293d0fb0]
(0x01000000:Folder):XMLNSC = ( ['xmlnsc' : 0x1293d1330]
(0x01000000:Folder)http://sample/iibadmin:EMPLOYEE = (
(0x03000102:NamespaceDecl):xmlns = 'http://sample/iibadmin' (CHARACTER)
(0x03000102:NamespaceDecl)http://www.w3.org/2000/xmlns/:ibmSchExtn = 'http://www.ibm.com/schema/extensions' (CHARACTER)
(0x03000102:NamespaceDecl)http://www.w3.org/2000/xmlns/:xsi = 'http://www.w3.org/2001/XMLSchema-instance' (CHARACTER)
(0x03000100:Attribute )http://www.w3.org/2001/XMLSchema-instance:schemaLocation = 'http://sample/iibadmin schema.xsd' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:EMPNO = '123456' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:FIRSTNME = 'test1' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:MIDINIT = 'P' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:LASTNAME = 'last1' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:WORKDEPT = 'IT' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:PHONENO = '999999999' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:HIREDATE = '2020-08-02' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:JOB = 'ESB' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:EDLEVEL = '16' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:SEX = 'M' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:BIRTHDATE = '1979-05-02' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:SALARY = '20000' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:BONUS = '500' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:COMM = '15000' (CHARACTER)
)
(0x01000000:Folder)http://sample/iibadmin:EMPLOYEE = (
(0x03000102:NamespaceDecl):xmlns = 'http://sample/iibadmin' (CHARACTER)
(0x03000102:NamespaceDecl)http://www.w3.org/2000/xmlns/:ibmSchExtn = 'http://www.ibm.com/schema/extensions' (CHARACTER)
(0x03000102:NamespaceDecl)http://www.w3.org/2000/xmlns/:xsi = 'http://www.w3.org/2001/XMLSchema-instance' (CHARACTER)
(0x03000100:Attribute )http://www.w3.org/2001/XMLSchema-instance:schemaLocation = 'http://sample/iibadmin schema.xsd' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:EMPNO = '123457' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:FIRSTNME = 'test2' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:MIDINIT = 'P' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:LASTNAME = 'last2' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:WORKDEPT = 'IT' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:PHONENO = '999999999' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:HIREDATE = '2020-08-02' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:JOB = 'ESB' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:EDLEVEL = '16' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:SEX = 'M' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:BIRTHDATE = '1979-05-02' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:SALARY = '20000' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:BONUS = '500' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:COMM = '15000' (CHARACTER)
)
(0x01000000:Folder)http://sample/iibadmin:EMPLOYEE = (
(0x03000102:NamespaceDecl):xmlns = 'http://sample/iibadmin' (CHARACTER)
(0x03000102:NamespaceDecl)http://www.w3.org/2000/xmlns/:ibmSchExtn = 'http://www.ibm.com/schema/extensions' (CHARACTER)
(0x03000102:NamespaceDecl)http://www.w3.org/2000/xmlns/:xsi = 'http://www.w3.org/2001/XMLSchema-instance' (CHARACTER)
(0x03000100:Attribute )http://www.w3.org/2001/XMLSchema-instance:schemaLocation = 'http://sample/iibadmin schema.xsd' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:EMPNO = '123458' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:FIRSTNME = 'test3' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:MIDINIT = 'P' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:LASTNAME = 'last3' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:WORKDEPT = 'IT' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:PHONENO = '999999999' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:HIREDATE = '2020-08-02' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:JOB = 'ESB' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:EDLEVEL = '16' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:SEX = 'M' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:BIRTHDATE = '1979-05-02' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:SALARY = '20000' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:BONUS = '500' (CHARACTER)
(0x03000000:PCDataField )http://sample/iibadmin:COMM = '15000' (CHARACTER)
)
)
)
) |
[/code] |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
Vitor |
Posted: Thu Jan 14, 2021 12:43 pm Post subject: |
|
|
![](images/avatars/905340816499abc1cdfb07.jpg) Grand High Poobah
Joined: 11 Nov 2005 Posts: 26093 Location: Texas, USA
|
Why did you change to
madrox wrote: |
Code: |
Declare sample NAMESPACE Value 'xyz'; |
|
which is wrong from
madrox wrote: |
Code: |
DECLARE xmlns NAMESPACE 'http://sample/iibadmin'; |
|
which has a confusing prefix but is correct.
Looking at your trace:
madrox wrote: |
Code: |
Declare sample NAMESPACE Value 'xyz';
s = get Environment.XMLNSC.sample:EMPLOYEE[1].sample:FIRSTNAME;
|
|
will not find
madrox wrote: |
Code: |
(0x03000000:PCDataField )http://sample/iibadmin:FIRSTNME = 'test1' (CHARACTER)
|
|
because it's looking for
It also doesn't help that the Trace says the field is FIRSTNME but your code says FIRSTNAME. Namespace prefixes notwithstanding, that's not going to go well. _________________ Honesty is the best policy.
Insanity is the best defence. |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
madrox |
Posted: Thu Jan 14, 2021 1:36 pm Post subject: |
|
|
![](images/avatars/367756885526ed8ee71d3.jpg) Acolyte
Joined: 11 Mar 2015 Posts: 71
|
I finally resolved the issue after multiple tries. Thank you everyone and fjb_saper i noticed the namespace on the element late. Below is the working code, FYI declaring namespace with 'Value' gives a syntax error.
Code: |
DECLARE iib NAMESPACE 'http://sample/iibadmin';
CREATE LASTCHILD OF Environment DOMAIN 'XMLNSC' NAME 'XMLNSC';
CREATE LASTCHILD OF Environment.XMLNSC PARSE (xml OPTIONS FolderBitStream CCSID 1208 FORMAT 'XMLNSC');
SET s = Environment.XMLNSC.XMLNSC.iib:EMPLOYEE[1].iib:FIRSTNME ;
SET OutputRoot.XMLNSC.FirstName = THE(SELECT ITEM R.iib:FIRSTNME FROM Environment.XMLNSC.XMLNSC.iib:EMPLOYEE[] AS R where R.iib:EMPNO=Empno);
|
![](images/smiles/clap.gif) |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
mgk |
Posted: Fri Jan 15, 2021 5:26 am Post subject: |
|
|
![](images/avatars/gallery/Star Wars/movie_star_wars_emperors_guard.gif) Padawan
Joined: 31 Jul 2003 Posts: 1638
|
I'm really glad it's working, but it can be improved. This code is actually creating two XMLNSC parsers (one nested inside the other) which is not very efficient:
Code: |
CREATE LASTCHILD OF Environment DOMAIN 'XMLNSC' NAME 'XMLNSC';
CREATE LASTCHILD OF Environment.XMLNSC PARSE (xml OPTIONS FolderBitStream CCSID 1208 FORMAT 'XMLNSC'); |
The reason is that your first line uses the DOMAIN clause which creates an XMLSNC parser, but then your PARSE with FORMAT on the second line creates another which happens to be nested inside the first. You can see this in the user trace you posted where your tree shows two xmlsnc parsers with different addresses which proves different parsers:
Code: |
( ['MQROOT' : 0x1258c3450]
(0x01000000:Folder):XMLNSC = ( ['xmlnsc' : 0x1293d0fb0]
(0x01000000:Folder):XMLNSC = ( ['xmlnsc' : 0x1293d1330] |
One quick fix for this would be to simply remove the DOMAIN clause from your first line and everything should keep working without the overhead of the second parser. A more complete solution would be to remove the first line completely and change the references from:
Code: |
Environment.XMLNSC.XMLNSC |
to
and so remove the second XMLNSC element which is not needed and caused some concern earlier in this thread.
I hope that helps. _________________ MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
madrox |
Posted: Fri Jan 15, 2021 6:13 am Post subject: |
|
|
![](images/avatars/367756885526ed8ee71d3.jpg) Acolyte
Joined: 11 Mar 2015 Posts: 71
|
mgk I have tried removing the first line but that resulted in a " Exception while parsing Structure id mismatch at offset" |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
fjb_saper |
Posted: Fri Jan 15, 2021 6:16 am Post subject: |
|
|
![](images/avatars/gallery/misc/2.gif) Grand High Poobah
Joined: 18 Nov 2003 Posts: 20696 Location: LI,NY
|
madrox wrote: |
mgk I have tried removing the first line but that resulted in a " Exception while parsing Structure id mismatch at offset" |
Did you try this?:
Code: |
CREATE LASTCHILD OF Environment PARSE (xml OPTIONS FolderBitStream CCSID 1208 FORMAT 'XMLNSC'); |
![](images/smiles/innocent.gif) _________________ MQ & Broker admin |
|
Back to top |
|
![](templates/DAJ_Glass/images/spacer.gif) |
|