| Author |
Message
|
| MBExpert |
 Posted: Fri Jan 15, 2010 1:40 am Post subject: Database insert of xml through ESQL Message Broker |
 |
|
Novice
Joined: 22 Aug 2007
Posts: 14
|
Hi,
I am using statement:
PASSTHRU ('INSERT INTO "<Schema>"."<TableName>" (Field1_Number , Field2_char , Field3_CLOB) Values (?,?,?)' ,1, 'Key1', OutputRoot.XML.Message.Data));
All fields are getting inserted, except the last one. ie OutputRoot.XML.Message.Data.
The Message (OutputRoot) looks like this:
<Message><Data name="abc" trace="2010-01-14 05:23:39.697326">
<Folder1 id="F001" name="Data1" trace="2010-01-14 05:23:39.697326">
<Folder2 id="F001-1" name="Data1 - Val1" trace="2010-01-14 05:23:39.697326">
<Folder3>
<Parameter name="P1">355</Parameter>
<Parameter name="P2">123</Parameter>
</Folder3>
</Folder2>
</Folder1>
</Data>
</Message>
The same code works for me if my xml does not have attributes.
I tried converting it to bitstream and then inserting as clob. I also tried using (XML.AsisElement) too.. But neither of them worked.
Any ideas?
Regards,
NK |
|
| Back to top |
|
 |
| exerk |
| Posted: Fri Jan 15, 2010 2:15 am Post subject: |
|
|
Jedi Council
Joined: 02 Nov 2006
Posts: 6339
|
Moving it to the Broker forum...
_________________
It's puzzling, I don't think I've ever seen anything quite like this before...and it's hard to soar like an eagle when you're surrounded by turkeys. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Jan 15, 2010 3:08 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Are you sure your message domain is XML?
What did you parse the incoming message into? Could it me XMLNSC
If you are using the XML domain then please use another one as this parser is depreciated. Kimbert no doubt will recommend XMLNSC.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| constantlearner |
| Posted: Fri Jan 15, 2010 8:42 am Post subject: |
|
|
Newbie
Joined: 08 Jan 2010
Posts: 8
|
Check what is getting inserted in your table.
One of the reasons why proper data is not getting inserted when you insert the xml can be because of control characters in it. For e.g. after you insert if you see in your table something like "/n /n" etc then it means that the XML is not normalized.
I'm not sure on how to normalize an xml i.e. remove all the unwanted spaces or characters.
Is there any command to do this in ESQL? |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jan 15, 2010 8:49 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| constantlearner wrote: |
| Is there any command to do this in ESQL? |
Use XMLNSC. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Jan 15, 2010 9:18 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| constantlearner wrote: |
Check what is getting inserted in your table.
One of the reasons why proper data is not getting inserted when you insert the xml can be because of control characters in it. For e.g. after you insert if you see in your table something like "/n /n" etc then it means that the XML is not normalized.
I'm not sure on how to normalize an xml i.e. remove all the unwanted spaces or characters.
Is there any command to do this in ESQL? |
From the clue given in the PASSTHRU I'd guess that the targer field is a CLOB.
so your points about the spaces etc is incorrect.
The input Data is probably in a parsed form of XML. It does not matter which domain XML, XMLNS or XMLNSC.
This is incompatible with the type of data that has to be used when inserting into CLOB/BLOB fields.
You need to convert the parsed form of XML into a BLOB format.
There are many,many posts here about how to do this.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| constantlearner |
| Posted: Fri Jan 15, 2010 9:19 am Post subject: |
|
|
Newbie
Joined: 08 Jan 2010
Posts: 8
|
| The XMLNSC may not solve the problem as the control characters will still be there inside the xml.... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jan 15, 2010 9:38 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| constantlearner wrote: |
| The XMLNSC may not solve the problem as the control characters will still be there inside the xml.... |
It's not clear why a CLOB field would have any difficulties with legal characters, even if they are non-printable.
It's fairly straightforward to understand why it will not work to insert a logical message tree into a database column that is expecting a character bitstream. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jan 15, 2010 9:51 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| constantlearner wrote: |
| The XMLNSC may not solve the problem as the control characters will still be there inside the xml.... |
No they won't.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
