Home
Login
Sign Up
Infor - IBM i-series Community
Profile Options
Tools
Edit Profile
Manage all aspects of your profile here including demographics, emails, notification settings, etc...
Change Password
Begin the password change process
Email Subscriptions
Manage your opt-in/opt-out email subscriptions
View Profile
View your profile - what other's see
Sign-out
Sign out of this community
1-800-819-1190
Contact support line
Smart Connect
Connect with others and manage various member options
View QR Code
View your profile QR Code - Connect and or share your contact information
Tour Page
Take an interactive tour of this page
Knowledge Base
Visit the knowledge base for more info
Insights
Member reporting and analysis
Terms of Use
Cookies
Privacy Policy
Contact Us
Help
 
 

Site Menu

Forum | Message
Querying LX Connector Inbox Table
James Reinardy Infor LX User Group
BC:

Question:

Article:

Querying LX Connector Inbox Table

Categorized as:

LX

Does anyone have a SQL query that can successfully decode the XML fields in the LXC_INBOX_ENTRY table? We have been doing it using a linked server from SQL Server but would like to change this to a native DB2 for i query as there are issues with the linked server leaving connections hanging. It appears to be XML text stored in a BLOB column, but we just have not hit the right combination to properly display it. Parsing the XML gives us an error that the closing Envelope tag is incomplete,  Read more...even though we believe the XML is perfectly fine. Show less...
Delivered Read
LX
6
344
Latest msg: Michael, that makes sense. We have few that are long like that but it is not the norm for us. Phil, glad this worked for you. from James Reinardy, on 05/28/2024

Comments (6) (Descending Chronological Order)
There are no comments at this time, be the first to comment

James Reinardy
BC:
Michael, that makes sense. We have few that are long like that but it is not the norm for us. Phil, glad this worked for you.
Michael Eck
BC:
Digging deeper, my issue is that my XMLs easily exceed 4000 characters and a lot even exceed the SQL limit of 32,717 characters.
Phil Catlin
BC:
Jim.. unlike Michael's experience... that sql statement worked fine for me. (Running in "run sql scripts" from Access Client Solutions tool). Thanks for the tip!!
Michael Eck
BC:
I, too, have been trying to find a way in interactive SQL or Run SQL Scripts to view the XML data. I have a Windows-based tool, Advanced Query Tool from Cardett Associates, that works well to view the data but still would like a solution for using the others. @jamesreinardy: I tried your xmlparse method but get the errors, "Query cannot be run. See lower level messages." which says, "User-defined function error on  Read more...member COR_O00002". If I'm missing something that your detailed instructions might help, I'll stand by and wait for them. Show less...
James Reinardy
BC:
Phil, thanks for taking the time to reply. I think I figured out a different way now that might work for you. This statement works for the most part: Select xmlparse(document cast(C_XML as varchar(4000) FOR BIT DATA) preserve whitespace) from LX841F.LXC_INBOX_ENTRY. We still have a problem where one of our applications that submits transactions for LX Connector is dropping off the last character of the closing tag. LX Connector doesn't care, but this statement does, so we have to fix  Read more...that bug before I can implement anything. I was trying to get this done before I retire, but looks like it might not happen now. I can at least leave detailed instructions.

Jim Show less...
Phil Catlin
BC:
James.. the content of that field is in CCSID(1208), at least in our case, the problem is that the table has that column defined as CCSID 65535, and there is no conversion table between 65535 and anything else. So I've never been able to write a SQL statement to convert it to anything.
I can retrieve it in a program and move it to a variable that has a CCSID of 1208... then I can do pretty much anything with it... the only other thing I've ever done is copy the field and paste it into  Read more...a "freeware" website that converts Hex to Text... In theory we could mod the table to change the CCSID of the blob.. then, in theory you'd be able to select cast(c_xml varchar(someLength) ccsid(37)) as XML from lxc_inbox_entry and get a valid result... Show less...

Online