Population of on-ramp tables (COR_OUTBOX_ENTRY)

Legacy Contributor

Hey guys

A client is writing data to the COR_OUTBOX_ENTRY and COR_OUTBOX_HEADER tables from their application using SQL.

The data they are writing to the C_XML column is being written thus: -

DECLARE @Disp XML
SET @Disp =
'<Products>
<Product>
<ID>25</ID>
<Name>Hamam</Name>
<Price>15.5</Price>
<Category name="IN">Infor</Category>
</Product>
</Products>'

Declare @DispBinary Varbinary(max)
set @DispBinary = CONVERT (varbinary(max),@Disp,1)

The value of @DispBinary is then being written to the C_XML column.

 

However, in OneView, the data is being displayed as: -

Does anybody have any thoughts on how the data should be formatted in SQL to correct this?

 

Thanks

Neil

Legacy Contributor

the short answer to this is that SQL cannot deal with this correctly in all cases via scripts, store procedures - you need to use a programming language like Java to do it 100%. This is because the C_XML binary needs to be encoded as UTF-8 which SQL does not support support - therefore once you move out side of the common characters to double byte for example you will have issues. However for most western character sets you can probably get the desired result but be careful of these edge cases. first thing to simply try is changing your @Disp variable to be a varchar or nvarchar and not an XML data type - then convert as you have to binary