Home
Login
Sign Up
 
 

Forum | Message

Tue, May, 28th 2024
Message Link »
6
View »
James Reinardy Infor LX User Group
BC:

Forum Question/Topic:

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...
LX
Delivered Read
6
Phil Catlin
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...
James Reinardy
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...
Michael Eck
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...
Phil Catlin
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
Digging deeper, my issue is that my XMLs easily exceed 4000 characters and a lot even exceed the SQL limit of 32,717 characters.
James Reinardy
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.

Online