| Author |
Message
|
| bprasana |
 Posted: Wed Apr 27, 2011 11:01 am Post subject: Passing XML to DB2 9.5 using WMB 6.1 |
 |
|
Disciple
Joined: 18 Apr 2005
Posts: 179
|
Hi,
I dont see any native support for passing XML from WMB 6.1 to DB2 9.5 XML field.
for eg: i have created a table following table in db2
create table items (
id int primary key not null,
brandname varchar(30),
itemname varchar(30),
sku int,
srp decimal(7,2),
comments xml
);
and use following statement from WMB 6.1.
PASSTHRU('insert into pbablesh.items values (?,?,?,?,?,?)',77, 'IBM', 'Gold', 1234,33.65,InputRoot.XMLNSC);
Thes insert is successful but the XML column contains no data. I am passing a well formed XML as input.
but the following works as expected.
PASSTHRU('insert into pbablesh.items values (?,?,?,?,?,?)',78, 'IBM', 'Gold', 1234,33.65,
'<addr>111 Main St., Dallas, TX, 00112</addr>');
I should be able to insert messages as big as 4 MB into the database. Is there any way that can be done?
Thanks,
bprasana |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Apr 27, 2011 11:07 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
What is the difference between '<addr>111 Main St., Dallas, TX, 00112</addr>' and InputRoot.XMLNSC?
Are they at all the same type of thing? Why or why not?
What does the documentation say on the support for XML column types? You say you don't see any native support ... where did you look for it? |
|
| Back to top |
|
|
| bprasana |
| Posted: Wed Apr 27, 2011 11:31 am Post subject: |
|
|
Disciple
Joined: 18 Apr 2005
Posts: 179
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 27, 2011 11:35 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 27, 2011 11:36 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| bprasana wrote: |
| I assumed it holds good for all types of interactions between db2 and WMB. |
It does, but you're still missing the point my most worthy comment about the difference between '<addr>111 Main St., Dallas, TX, 00112</addr>' and InputRoot.XMLNSC. Which explains why one works and one doesn't.
You also asked about "native" support. If you mean can WMB support this:
| Quote: |
| XML values are processed in an internal representation that is not a string and not directly comparable to string values |
then no, I don't believe WMB directly supports any DB2 internal structures.
<fish>The converse is of course also true</fish> 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| bprasana |
| Posted: Wed Apr 27, 2011 12:30 pm Post subject: |
|
|
Disciple
Joined: 18 Apr 2005
Posts: 179
|
InputRoot.XMLNSC is a canonical representation of incoming message.
But that's not the point. I just wanted to 'mention' that , I just tried 2-3 different ways to pass XML and this was one did not work.
Any thoughts on, if it can be achieved? |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 27, 2011 12:39 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| bprasana wrote: |
| InputRoot.XMLNSC is a canonical representation of incoming message. |
No it isn't. There's nothing canonical about it. Where did you obtain that impression?
Also you need to define what you mean by "representation", as it's exactly the point as both mqjeff and I have said.
| bprasana wrote: |
| Any thoughts on, if it can be achieved? |
By understanding what InputRoot.XMLNSC is and dealing with it accordingly. One fish per day is all I give out.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| bprasana |
| Posted: Wed Apr 27, 2011 12:53 pm Post subject: |
|
|
Disciple
Joined: 18 Apr 2005
Posts: 179
|
its Logical Message Tree of the input message.
How would it help ?
I wonder if I can use an external java code or a c, c++ code to insert an XML into the database? |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 27, 2011 1:00 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| bprasana wrote: |
| its Logical Message Tree of the input message. |
Which is not in a canonical format but is simply a logical representation of the original document. It's also in most cases not a complete representation due to the fact that WMB performs on-demand parsing so the tree is incomplete unless you've parsed all the elements (or changed the parsing model).
| bprasana wrote: |
| How would it help ? |
Ok, another fish (just got some good news). The point that I (and I suspect mqjeff) have been trying to make is that '<addr>111 Main St., Dallas, TX, 00112</addr>' (which worked) is a character string. By your own admission InputRoot.XMLNSC is a logical tree not a string.
Of course it can be a string. If you do that.
| bprasana wrote: |
| I wonder if I can use an external java code or a c, c++ code to insert an XML into the database? |
Of course you can. Not sure why you'd want to, but you might be sure you want to. This cat can be skinned any number of ways. The Java or C code wouldn't have to be external.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| bprasana |
| Posted: Wed Apr 27, 2011 1:15 pm Post subject: |
|
|
Disciple
Joined: 18 Apr 2005
Posts: 179
|
okie got it! Thank You.
| Quote: |
| This cat can be skinned any number of ways. The Java or C code wouldn't have to be external. |
Can you elaborate on this please.
Are you saying this can be done using esql? any idea how it can be done ?
note: i need to be able to put a 4 MB message to that xml column. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 27, 2011 2:14 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| bprasana wrote: |
| Quote: |
| This cat can be skinned any number of ways. The Java or C code wouldn't have to be external. |
Can you elaborate on this please. |
WMB can run Java & C code internally. I'm not saying it's a good idea, or bad idea, or the right idea, just that it can be done. It's all documented.
| bprasana wrote: |
| Are you saying this can be done using esql? |
Yes.
| bprasana wrote: |
| any idea how it can be done ? |
Yes. 2 fish a day is my limit. Try thinking about the "it" you've now "got".
| bprasana wrote: |
| note: i need to be able to put a 4 MB message to that xml column. |
And this is relevant to this discussion in what context? 
The "restriction" here is that that DB2 data type will only accept a well formed XML document not the entire message. So you'll need to handle the headers of any message separately. But I'd assumed you'd already grasped that as you keep banging on about the XMLNSC part of the input tree.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 27, 2011 4:24 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Here's my second fish for the day.
What's the difference between a string of characters and a string of bytes? |
|
| Back to top |
|
|
| bprasana |
| Posted: Wed Apr 27, 2011 6:44 pm Post subject: |
|
|
Disciple
Joined: 18 Apr 2005
Posts: 179
|
| So can i use any internal ESQL functions to convert incoming XML to String and then insert to the database? |
|
| Back to top |
|
|
| flahunter |
| Posted: Wed Apr 27, 2011 6:52 pm Post subject: |
|
|
Acolyte
Joined: 30 Oct 2008
Posts: 62
|
| Yes, I think you can serialize the XML and insert as the char datatype into the database. That's what I have already tried before |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Apr 28, 2011 12:56 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
You can do this a couple of ways:
a) Change the domain to 'BLOB' on the input node, and then use the ESQL CAST function to convert InputRoot.BLOB.BLOB to a CHARACTER string
or
b) if you need to parse the input XML document using XMLNSC ( because your message flow needs to work on the parsed message tree ), then you can convert InputRoot.XMLNSC to a BLOB using the ASBITSTREAM function. Then do a) to get the CHARACTER string that you require for the insert.
Please don't ask how to use CAST and ASBITSTREAM. Look in the infocenter or search this forum for examples. |
|
| Back to top |
|
|
|
|
