| Author |
Message
|
| Bravo |
 Posted: Wed Dec 20, 2006 9:39 am Post subject: Esql help on string to xml |
 |
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
Hi All,
I'm etracting rows from DB2 which is stored in string as XML format.
esql
| Code: |
| SET InputLocalEnvironment.ResultSet[] = (SELECT T.GENRC_DATA_CHAR, T.GENRC_DATA_DEC FROM Database.WBIMBADM.WBIMB_FLOW_WIP AS T where T.INTRFC_ID = charINTRFC_ID); |
Then I'm converting to XML
| Code: |
CREATE LASTCHILD OF Environment DOMAIN('XML') PARSE(InputLocalEnvironment.ResultSet[1].GENRC_DATA_CHAR, InputProperties.Encoding,InputProperties.CodedCharSetId);
DECLARE I INTEGER 2;
DECLARE J INTEGER CARDINALITY(InputLocalEnvironment.ResultSet[]);
WHILE I <= J DO
CREATE NEXTSIBLING OF Environment.XML PARSE(InputLocalEnvironment.ResultSet[I].GENRC_DATA_CHAR, InputProperties.Encoding,InputProperties.CodedCharSetId);
SET I = I + 1;
END WHILE; |
But I'm getting three Environmet.XML
Environmet.XML[1].Data
Environmet.XML[2].Data
Environmet.XML[3].Data
but expected result is
Environmet.XML.Data[1]
Environmet.XML.Data[2]
Environmet.XML.Data[3]
Any suggestion.
_________________
Bravo |
|
| Back to top |
|
 |
| special_agent_Queue |
| Posted: Wed Dec 20, 2006 9:44 am Post subject: |
|
|
Centurion
Joined: 27 Jul 2006
Posts: 102
|
| You are creating nextsibling of Environment.XML. So the nextsibling would be Environment.XML[2]. So this is the expected result given your code. |
|
| Back to top |
|
|
| Bravo |
| Posted: Wed Dec 20, 2006 9:52 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
Hi even tried with the following esql
| Code: |
CREATE LASTCHILD OF Environment DOMAIN('XML') PARSE(InputLocalEnvironment.ResultSet[1].GENRC_DATA_CHAR, InputProperties.Encoding,InputProperties.CodedCharSetId);
DECLARE I INTEGER 2;
DECLARE J INTEGER CARDINALITY(InputLocalEnvironment.ResultSet[]);
WHILE I <= J DO
CREATE LASTCHILD OF Environment.XML PARSE(InputLocalEnvironment.ResultSet[I].GENRC_DATA_CHAR, InputProperties.Encoding,InputProperties.CodedCharSetId);
SET I = I + 1;
END WHILE; |
The output was
if I is 1 then Environment.XML.DATA;
if I is 2 then Environment.XML.DATA.XML.DATA;
Could you please point me out what I'm missing to get my expected result.
_________________
Bravo |
|
| Back to top |
|
|
| special_agent_Queue |
| Posted: Wed Dec 20, 2006 10:14 am Post subject: |
|
|
Centurion
Joined: 27 Jul 2006
Posts: 102
|
How about something like....
| Code: |
CREATE LASTCHILD OF Environment DOMAIN('XML') PARSE(InputLocalEnvironment.ResultSet[1].GENRC_DATA_CHAR, InputProperties.Encoding,InputProperties.CodedCharSetId);
DECLARE Pointer REFERENCE TO Environment.XML.*[1];
DECLARE I INTEGER 2;
DECLARE J INTEGER CARDINALITY(InputLocalEnvironment.ResultSet[]);
WHILE I <= J DO
CREATE NEXTSIBLING OF Pointer PARSE(InputLocalEnvironment.ResultSet[I].GENRC_DATA_CHAR, InputProperties.Encoding,InputProperties.CodedCharSetId);
SET I = I + 1;
END WHILE; |
** NOTE: this is untested. |
|
| Back to top |
|
|
| Bravo |
| Posted: Wed Dec 20, 2006 10:33 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
I test the provided code and the o/p looks like
Environment.XML.XML.DATA[3];
Environment.XML.XML.DATA[2];
Environment.XML.DATA[1];
_________________
Bravo |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Dec 20, 2006 10:50 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Bravo,
Try this...not tested ofcourse...
| Code: |
DECLARE rResultSet REFERENCE TO InputLocalEnvironment.ResultSet;
DECLARE bFirstResultSet BOOLEAN true;
-- Create a reference to use later
DECLARE rEnv REFERENCE TO InputRoot;
WHILE LASTMOVE(rResultSet) DO
IF (rResultSet) THEN
CREATE FIELD Environment.XML AS rEnv DOMAIN 'XML';
SET bFirstResultSet = false;
ELSE
CREATE NEXTSIBLING OF rEnv AS rEnv REPEAT;
END IF;
CREATE LASTCHILD OF rEnv PARSE(rResultSet.GENRC_DATA_CHAR, InputProperties.Encoding,InputProperties.CodedCharSetId);
MOVE rResultSet NEXTSIBLING NAME 'ResultSet';
END WHILE; |
Regards. |
|
| Back to top |
|
|
| Bravo |
| Posted: Wed Dec 20, 2006 11:00 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
Hi elvis_gn;
I'm getting syntax error for the following statement
| Code: |
| CREATE FIELD Environment.XML AS rEnv DOMAIN 'XML'; |
_________________
Bravo |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Dec 20, 2006 11:10 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Bravo,
Sorry, my mistake....FIELD cannot take DOMAIN...try this instead.
| Code: |
| CREATE LASTCHILD OF Environment AS rEnv DOMAIN 'XML' NAME 'XML'; |
My fingers are crossed 
Regards. |
|
| Back to top |
|
|
| Bravo |
| Posted: Wed Dec 20, 2006 11:28 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
Thanks elvis but no luck. Getting exception in the follwoing code.
| Code: |
| CREATE NEXTSIBLING OF rEnv AS rEnv REPEAT; |
IF (rResultSet) THEN fails at first and try to execute above code.
getting error BIP2536E Inappropriate field reference
_________________
Bravo |
|
| Back to top |
|
|
| special_agent_Queue |
| Posted: Wed Dec 20, 2006 11:29 am Post subject: |
|
|
Centurion
Joined: 27 Jul 2006
Posts: 102
|
Oops. I forgot to MOVE Pointer NEXTSIBLING as the last statement of the loop.
Then I realized that:
each ResultSet starts with XML.Data
Therefore, you are going to end up with multiple Environment.XML fields.
You can correct this by parsing before moving it to the Environment.XML.*[I].
Or you can just code whatever depends on this to expect it in a different form. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Dec 20, 2006 11:43 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Bravo,
It shouldn't be
| Code: |
| IF (rResultSet) THEN |
but
| Code: |
| IF (bFirstResultSet) THEN |
 Bad day...sorry, this is my last attempt 
Regards. |
|
| Back to top |
|
|
| Bravo |
| Posted: Wed Dec 20, 2006 1:26 pm Post subject: |
|
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
code executed with no errors but o/p is not as expected.
Environment.XML[1].XML.Data
Environment.XML[2].XML.Data
Environment.XML[3].XML.Data
_________________
Bravo |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Dec 20, 2006 1:35 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Please don't use the XML domain.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Dec 20, 2006 1:46 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| bravo : Use the XMLNSC domain ( never use XML domain for new message flows ) |
|
| Back to top |
|
|
| Bravo |
| Posted: Wed Dec 20, 2006 1:53 pm Post subject: |
|
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
Kimbert and jeff.We are using WBIMB V5.I think we cannot use XMLNSC in Ver 5.
Sorry for not mentioning earlier
_________________
Bravo |
|
| Back to top |
|
|
|
|
