| Author |
Message
|
| Vjenceslav |
 Posted: Mon Sep 03, 2007 4:15 am Post subject: Message Broker 6 - reading XML message from one column in DB |
 |
|
Newbie
Joined: 24 Apr 2006
Posts: 7
|
Hello!
I would like to ask how can i parse XML message to message broker witch is saved in _one_ column in DB? I know how to do it in normal way (MQ Input + XML parser) but I was talking with some ppl and they just told me about some BLOB parsing if i am taking big XML message from one column.. but to tell you the truth nobody give me some valid answer. So is there any info on net or in books (or you can help me here)?
Thank you! |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Mon Sep 03, 2007 11:20 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Look up the create command:
create lastchild of xxxx PARSE...
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| AkankshA |
| Posted: Mon Sep 03, 2007 11:25 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
to store the message in one column create a bitstream of it using ASBITSTREAM function
to get it back in format use PARSE as fjb_saper suggested
CREATE LASTCHILD OF <> DOMAIN('XML') PARSE <>
_________________
Cheers |
|
| Back to top |
|
|
| Vjenceslav |
| Posted: Mon Sep 03, 2007 11:36 pm Post subject: |
|
|
Newbie
Joined: 24 Apr 2006
Posts: 7
|
well guys thanks on answers but as we know world is not perfect  and data that i need to read from DB is already saved by the customer (suprise, suprise) and it is in TEXT type DB column (Oracle). So how do I read text data from one DB column (witch is XML ofc) and parse it with XML parser to MB? The same way as blob data? |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Mon Sep 03, 2007 11:59 pm Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
That depends upon the variable type in which you save you DB Column data.
Otherwise you just have to specify this variable in the PARSE.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| Vjenceslav |
| Posted: Sun Sep 09, 2007 10:15 am Post subject: |
|
|
Newbie
Joined: 24 Apr 2006
Posts: 7
|
hi! well i managed to find something in information center...
here
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ak05810_.htm
and managed to do most of things, i have used this part of code
SET Environment.Variables.DBResult = THE( SELECT T.XML_MSG FROM Database.MYTABLE AS T);
DECLARE resultRef REFERENCE TO Environment.Variables.DBResult;
IF LASTMOVE(resultRef) THEN
DECLARE outCCSID INT resultRef.MSGCCSID;
DECLARE outEncoding INT resultRef.MSGENCODING;
DECLARE outMsg BLOB CAST(resultRef.MSGDATA AS BLOB CCSID outCCSID);
SET OutputRoot.Properties.CodedCharSetId = outCCSID;
SET OutputRoot.Properties.Encoding = outEncoding;
CREATE LASTCHILD OF OutputRoot DOMAIN('XMLNS') PARSE(outMsg, outEncoding, outCCSID);
-- Now modify the message tree fields
SET OutputRoot.XMLNS.A.B = 4;
SET OutputRoot.XMLNS.A.E = 5;
END IF;
because i am reading XML data from varchar column so I need it to parse it first to BLOB
ofc i have set up database, i manage to read the data from my column witch is in varchar format (i can see that in debug that DBResult variable has my XML data)
but messages were not comming out .. BECAUSE in this line
DECLARE outMsg BLOB CAST(resultRef.MSGDATA AS BLOB CCSID outCCSID);
i've got error! - what error i dunno because in debug mode i got red exclamation mark above MQInput node and TONS of RecoverableExceptions under ExceptionList in Variables in debug mode..
obviously problem is in casting..but hopefully someone knows this better then me  |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Sun Sep 09, 2007 2:37 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
It looks to me like you're only selecting a field called XML_MSG into DBResult.
And so DBResult won't have any children, especially any children named MSGDATA.
Maybe I'm wrong, though.
Can you put the SELECT statement into a different Compute node (temporarily), and then put a trace node in between it and the node that does the CREATE... PARSE...?
If you look at the tree that results from the SELECT, you'll get a better idea of what's going wrong.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Vjenceslav |
| Posted: Sun Sep 09, 2007 10:17 pm Post subject: |
|
|
Newbie
Joined: 24 Apr 2006
Posts: 7
|
well the point is that after
SET Environment.Variables.DBResult = THE( SELECT T.XML_MSG FROM Database.MYTABLE AS T);
I have in DebugMessage->Enviroment->Variables->Dbresult = my xml message from XML_MSG column
so it look all fine
also after
DECLARE resultRef REFERENCE TO Environment.Variables.DBResult;
variable resultRef has that XML message also (also checked in debugger)
that is why i am confused... |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Sep 10, 2007 12:54 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Right, but then you create your reference that points to Environment.Variables.DbResult.
And then you try and access Environment.Variables.DbResult.MSGDATA, which likely doesn't exist.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Vjenceslav |
| Posted: Mon Sep 10, 2007 5:33 am Post subject: |
|
|
Newbie
Joined: 24 Apr 2006
Posts: 7
|
| jefflowrey wrote: |
Right, but then you create your reference that points to Environment.Variables.DbResult.
And then you try and access Environment.Variables.DbResult.MSGDATA, which likely doesn't exist. |
Oh that!
ah belive me .. i have tried all other ways...
only with Environment.Variables.DbResult
then by my own variables.. etc etc.. every way finished in the same way...
exception...  |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Sep 10, 2007 10:46 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If you separate the select and the other code, and put a trace node in between (set to write to a file), then you will SEE the structure of the tree and elements that is created by the SELECT.
Then you will either understand how your code needs to be changed to get at the data you want, or you will have something that you can paste in a message here, and we can help you further.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Vjenceslav |
| Posted: Mon Sep 10, 2007 1:58 pm Post subject: |
|
|
Newbie
Joined: 24 Apr 2006
Posts: 7
|
hello jefflowrey, tx on the tip..i just did that and yes in trace i do get my XML message out (but only when i use Environment.Variables.DBResult and NOT Environment.Variables.DBResult.MSGDATA
so i presumed that also resultRef.MSGCCSID and resultRef.MSGENCODING in lines
DECLARE outCCSID INT resultRef.MSGCCSID;
DECLARE outEncoding INT resultRef.MSGENCODING;
also have only philosophical meaning so i changed that to
DECLARE outCCSID INT 437;
DECLARE outEncoding INT 546;
and VOILA! i did get slight movment forward
and now line
DECLARE outMsg BLOB CAST(resultRef AS BLOB CCSID outCCSID);
is working just great! does not throw error, BUT in debugger i don't see ANYTHING in outMsg (yes resultRef has my XML message...) and now in line
CREATE LASTCHILD OF OutputRoot DOMAIN('XMLNS') PARSE(outMsg, outEncoding, outCCSID);
i get error... uf.. (probably because outMsg is empty...) |
|
| Back to top |
|
|
|
|
