Illegal characters in XML export

[Former Member]

Hi,

We run SAP Business One 2007 PL37.

On the Item Master Data, our product manager inserts descriptions on our products under Remarks (OITM.UserText).

This information is gathered from supplier's websites and is inserted with copy/paste.

However, we regularly export our data back and forth between several systems and some of the characters that have been pasted in turn out as invalid XML characters even though they are accepted into SAP through the client.

Is there any way to "clean" these characters from OITM.UserText?

Thanks,

Mike

[Former Member]

Sorry for not getting back earlier.

It seems it's not the HTML tags that are causing but instead special characters like carriage return, local characters (æøå).

However, it seems that the iBOLT team may have discovered a solution for this. I'll be testing it with them later today and I'll post an update at this time.

Thanks again for all your help

[former_member201110]

Hi Mike,

Which illegal characters are causing you problems? What encoding are you using in your XML? Also, what method are you using to extract the data from SBO in to XML?

Kind Regards,

Owen

[Former Member]

It's a variety of characters ranging from "em dash" to Danish characers (only in some cases): æ, ø, å.

The XML files come out using encoding UTF-16:

<?xml version="1.0" encoding="UTF-16"?>

I'm not entirely sure how the data is extracted but I believe it's using the XML Save function on the SDK. The program that I'm using in between the 2 SAPs is iBOLT3.

Here's an example of a file that is corrupted with illegal characters and as you can see, some of the characters are messing up the XML. The thing I don't understand is how it can be exported from SAP without any error since it's obviously flawed.

Any suggestions on how to "clean" up these texts so that we don't risk our users entering invalid characters?

<?xml version="1.0" encoding="UTF-16"?><BOM><BO><AdmInfo><Object>4</Object><Version>2</Version></AdmInfo><Items><row><ItemCode>57355</ItemCode><ItemName>Fallout 3 Collectors Edition(ibolt) update /X360</ItemName><ItemsGroupCode>115</ItemsGroupCode><CustomsGroupCode>-1</CustomsGroupCode><SalesVATGroup>S1</SalesVATGroup><BarCode>93155125742</BarCode><VatLiable>tYES</VatLiable><PurchaseItem>tYES</PurchaseItem><SalesItem>tYES</SalesItem><InventoryItem>tYES</InventoryItem><SupplierCatalogNo>UBI</SupplierCatalogNo><DesiredInventory>0.000000</DesiredInventory><MinInventory>0.000000</MinInventory><Picture>53349-forside.jpg</Picture><User_Text>The Fallout series have long been hailed as some of the greatest RPG games ever. &amp;lt;br&amp;gt;With this the third game in the series die-hard fans and new players will see how far the game has progressed since its illustrious predecessors. &amp;lt;br&amp;gt;&amp;lt;br&amp;gt;

 	 The Fallout 3 Collector&#39;s Edition includes: (Not finally confirmed)&amp;lt;br&amp;gt;&amp;lt;br&amp;gt;

    * Fallout 3: From the creators of the award-winning Oblivion comes one of the most realized game worlds ever created. Create any kind of character you want and explore the open wastes of post-apocalyptic Washington, D.C. Every minute is a fight for survival as you encounter Super Mutants, Ghouls, Raiders, and other dangers of the Wasteland. Prepare for the future.&amp;lt;br&amp;gt;
    * Vault Boy Bobblehead: Enjoy your very own Vault Boy with this collectoru2019s item direct from Vault-Tec.&amp;lt;br&amp;gt;
    * The Art of Fallout 3: This hardcover book features nearly 100 pages of never-before seen concept art and commentary from Bethesda Game Studios artists.&amp;lt;br&amp;gt;
    * The Making of Fallout 3: Get an exclusive, inside look at Bethesda Game Studios and the team behind the game with this special DVD.&amp;lt;br&amp;gt;
    * Vault-Tec Lunch Box: The entire package comes in a fully customized metal lunchbox.
&amp;lt;br&amp;gt;&amp;lt;br&amp;gt;&amp;lt;br&amp;gt;</User_Text><CommissionPercent>0.000000</CommissionPercent><CommissionSum>0.000000</CommissionSum><CommissionGroup>0</CommissionGroup><TreeType>iNotATree</TreeType><AssetItem>tNO</AssetItem><Manufacturer>39</Manufacturer><ManageSerialNumbers>tNO</ManageSerialNumbers><ManageBatchNumbers>tNO</ManageBatchNumbers><Valid>tNO</Valid><ValidRemarks></ValidRemarks><Frozen>tNO</Frozen><FrozenRemarks></FrozenRemarks><SalesItemsPerUnit>1.000000</SalesItemsPerUnit><SalesQtyPerPackUnit>1.000000</SalesQtyPerPackUnit><SalesUnitLength>0.000000</SalesUnitLength><SalesUnitWidth>0.000000</SalesUnitWidth><SalesUnitHeight>0.000000</SalesUnitHeight><SalesUnitVolume>0.000000</SalesUnitVolume><SalesVolumeUnit>2</SalesVolumeUnit><SalesUnitWeight>0.500000</SalesUnitWeight><SalesWeightUnit>3</SalesWeightUnit><PurchaseItemsPerUnit>1.000000</PurchaseItemsPerUnit><PurchaseQtyPerPackUnit>1.000000</PurchaseQtyPerPackUnit><PurchaseUnitLength>0.000000</PurchaseUnitLength><PurchaseUnitWidth>0.000000</PurchaseUnitWidth><PurchaseUnitHeight>0.000000</PurchaseUnitHeight><PurchaseUnitVolume>0.000000</PurchaseUnitVolume><PurchaseVolumeUnit>2</PurchaseVolumeUnit><PurchaseUnitWeight>0.500000</PurchaseUnitWeight><PurchaseWeightUnit>3</PurchaseWeightUnit><PurchaseVATGroup>K1</PurchaseVATGroup><SalesFactor1>1.000000</SalesFactor1><SalesFactor2>1.000000</SalesFactor2><SalesFactor3>1.000000</SalesFactor3><SalesFactor4>1.000000</SalesFactor4><PurchaseFactor1>1.000000</PurchaseFactor1><PurchaseFactor2>1.000000</PurchaseFactor2><PurchaseFactor3>1.000000</PurchaseFactor3><PurchaseFactor4>1.000000</PurchaseFactor4><AvgStdPrice>0.000000</AvgStdPrice><ShipType>6</ShipType><GLMethod>glm_WH</GLMethod><TaxType>tt_Yes</TaxType><MaxInventory>0.000000</MaxInventory><ManageStockByWarehouse>tYES</ManageStockByWarehouse><PurchaseUnitHeight1>0.000000</PurchaseUnitHeight1><PurchaseUnitLength1>0.000000</PurchaseUnitLength1><PurchaseUnitWeight1>0.000000</PurchaseUnitWeight1><PurchaseUnitWidth1>0.000000</PurchaseUnitWidth1><SalesUnitHeight1>0.000000</SalesUnitHeight1><SalesUnitLength1>0.000000</SalesUnitLength1><SalesUnitWeight1>0.000000</SalesUnitWeight1><SalesUnitWidth1>0.000000</SalesUnitWidth1><ForceSelectionOfSerialNumber>tYES</ForceSelectionOfSerialNumber><ManageSerialNumbersOnReleaseOnly>tNO</ManageSerialNumbersOnReleaseOnly><WTLiable>tYES</WTLiable><CostAccountingMethod>bis_FIFO</CostAccountingMethod><WarrantyTemplate></WarrantyTemplate><IndirectTax>tNO</IndirectTax><IssueMethod>im_Backflush</IssueMethod><SRIAndBatchManageMethod>bomm_OnEveryTransaction</SRIAndBatchManageMethod><IsPhantom>tNO</IsPhantom><PlanningSystem>bop_None</PlanningSystem><ProcurementMethod>bom_Buy</ProcurementMethod><OrderMultiple>0.000000</OrderMultiple><MinOrderQuantity>0.000000</MinOrderQuantity><ItemType>itItems</ItemType><Properties1>tNO</Properties1><Properties2>tNO</Properties2><Properties3>tNO</Properties3><Properties4>tNO</Properties4><Properties5>tNO</Properties5><Properties6>tNO</Properties6><Properties7>tNO</Properties7><Properties8>tNO</Properties8><Properties9>tNO</Properties9><Properties10>tNO</Properties10><Properties11>tNO</Properties11><Properties12>tNO</Properties12><Properties13>tNO</Properties13><Properties14>tNO</Properties14><Properties15>tNO</Properties15><Properties16>tNO</Properties16><Properties17>tYES</Properties17><Properties18>tNO</Properties18><Properties19>tNO</Properties19><Properties20>tNO</Properties20><Properties21>tNO</Properties21><Properties22>tNO</Properties22><Properties23>tNO</Properties23><Properties24>tNO</Properties24><Properties25>tNO</Properties25><Properties26>tNO</Properties26><Properties27>tNO</Properties27><Properties28>tNO</Properties28><Properties29>tNO</Properties29><Properties30>tNO</Properties30><Properties31>tYES</Properties31><Properties32>tNO</Properties32><Properties33>tNO</Properties33><Properties34>tNO</Properties34><Properties35>tNO</Properties35><Properties36>tNO</Properties36><Properties37>tNO</Properties37><Properties38>tNO</Properties38><Properties39>tNO</Properties39><Properties40>tNO</Properties40><Properties41>tNO</Properties41><Properties42>tNO</Properties42><Properties43>tNO</Properties43><Properties44>tNO</Properties44><Properties45>tNO</Properties45><Properties46>tNO</Properties46><Properties47>tNO</Properties47><Properties48>tNO</Properties48><Properties49>tNO</Properties49><Properties50>tNO</Properties50><Properties51>tNO</Properties51><Properties52>tNO</Properties52><Properties53>tNO</Properties53><Properties54>tNO</Properties54><Properties55>tNO</Properties55><Properties56>tNO</Properties56><Properties57>tNO</Properties57><Properties58>tNO</Properties58><Properties59>tNO</Properties59><Properties60>tNO</Properties60><Properties61>tNO</Properties61><Properties62>tNO</Properties62><Properties63>tNO</Properties63><Properties64>tNO</Properties64><AutoCreateSerialNumbersOnRelease>tNO</AutoCreateSerialNumbersOnRelease><U_BOY_TB_0>N</U_BOY_TB_0><U_BOY_45_12><a href="http://fallout.bethsoft.com/teaser.html" TARGET="test_blank">http://fallout.bethsoft.com/teaser.html</a></U_BOY_45_12><U_BOY_45_2>20081031</U_BOY_45_2><U_BOY_45_6>RPG</U_BOY_45_6><U_BOY_45_1>N</U_BOY_45_1><U_BOY_45_5>Xbox</U_BOY_45_5><U_BOY_45_13><a href="http://fallout.bethsoft.com/" TARGET="test_blank">http://fallout.bethsoft.com/</a></U_BOY_45_13><U_BOY_45_4>AAA</U_BOY_45_4><U_BOY_45_3>N</U_BOY_45_3><U_BOY_45_10>N</U_BOY_45_10><U_BOY_45_19>473560</U_BOY_45_19><U_ET_StckPs></U_ET_StckPs><U_ET_1_2>266,585,586,587,588,589</U_ET_1_2></row></Items><Items_Prices><row><PriceList>1</PriceList><Price>599.000000</Price><Currency>DKK</Currency></row><row><PriceList>2</PriceList><Price>0.000000</Price><Currency>GBP</Currency></row><row><PriceList>3</PriceList><Price>639.200000</Price><Currency>DKK</Currency></row><row><PriceList>4</PriceList><Price>83.100000</Price><Currency>DKK</Currency></row><row><PriceList>5</PriceList></row><row><PriceList>6</PriceList><Price>244.800000</Price><Currency>NOK</Currency></row><row><PriceList>7</PriceList></row><row><PriceList>8</PriceList></row><row><PriceList>9</PriceList><Price>0.000000</Price><Currency>SEK</Currency></row><row><PriceList>10</PriceList><Price>0.000000</Price><Currency>EUR</Currency></row></Items_Prices><ItemWarehouseInfo><row><MaximalStock>0.000000</MaximalStock><MinimalOrder>0.000000</MinimalOrder><StandardAveragePrice>0.000000</StandardAveragePrice><Locked>tNO</Locked><WarehouseCode>01</WarehouseCode></row><row><MinimalStock>0.000000</MinimalStock><MaximalStock>0.000000</MaximalStock><MinimalOrder>0.000000</MinimalOrder><StandardAveragePrice>0.000000</StandardAveragePrice><Locked>tNO</Locked><WarehouseCode>02</WarehouseCode></row><row><MinimalStock>0.000000</MinimalStock><MaximalStock>0.000000</MaximalStock><MinimalOrder>0.000000</MinimalOrder><StandardAveragePrice>0.000000</StandardAveragePrice><Locked>tNO</Locked><WarehouseCode>03</WarehouseCode></row></ItemWarehouseInfo></BO></BOM>

[former_member201110]

Hi Mike,

That certainly looks like one of the DI API schemata for an item. I can't see any illegal characters in the usertext but I can see some HTML markup code (the &lt;br&gt; bits which are <br> tags). Is this what you are referring to or has this forum converted your XML when it displayed it (it does that sometimes, especially when using the code tags)? If the source of this text is a website then that might explain how HTML markup tags sometimes get included in the copied text.

There are probably a few ways you can set up routines to automatically clean the data. You could write an addon that checks the remarks field when the user clicks on Add or Update in the Item Master window. The addon would read the current value in the remarks field and replace unwanted strings or characters. This would ensure that the data was as clean as possible when it is created. The alternative would be to clean the XML data once it has been exported from SBO. How you'd achieve this depends on what program you are using to export the data. If your company has written the interface then it should be quite easy to add code to read the SBO XML and do a find and replace for unwanted strings or characters before passing the XML on to the other systems. If you are using an integration tool such as iBolt to pass data then an additional step in the flow could map the XML while filtering the usertext for unwanted text.

How effective the cleaning process is will depend on what additional characters are being added accidentally. If it's just HTML markup then you should be able to check for the most common tags easily and remove them.

I hope that makes sense.

Kind Regards,

Owen

P,S. Fallout 3....good game