Building an XRDF file to import an XML file

siredge

Forums

I received an XML file from a client.  I attempted to go through the import wizard and import the file.  I followed the prompts and told it to import all of the fields.  The result was not usable (no surprise there).
So now I am trying to create an XRDF file with the field definitions and I have no idea what the file should look like and I have been unable to find any guidance on what it should contain.  I've spent quite a while on Google trying to locate it (perhaps using the wrong search terms) and I'm coming up dry.  Any help?

Brian Element Fri, 08/30/2013 - 18:35

You can view the xrdf file in notepad and edit it in notepad. Below is an example from the samples that come with idea, this is for the OrderDetails file. If you open up the OrderDetails file in notepad or internet explorer you can see the link between the two files. Such as the XML_FIELD_NAME is the xml field name, the Context is the location in the xml file of the data and the IDEA__FIELD_NAME is the name of the IDEA field the info will be placed in. Hopefully this helps out a bit. This is a simple example but maybe you can use it as a reference. Instead of importing all the fields did you try just importing certain fields and then doing a join, that might work.

<?xml version="1.0" standalone="yes"?>
<!-- IDEA XML Import record definition file -->

<IDEA_IMPORT_FIELDS xmlns:od="urn:schemas-microsoft-com:officedata">
	<DE_SEP>.</DE_SEP>
	<TH_SEP>,</TH_SEP>
	<ROOT_TAG_NAME>dataroot</ROOT_TAG_NAME>
	<FIELD>
		<XML_FIELD_NAME>Discount</XML_FIELD_NAME>
		<XML_FIELD_CONTEXT>dataroot/Order_x0020_Details/Discount</XML_FIELD_CONTEXT>
		<IDEA_FIELD_NAME>DISCOUNT</IDEA_FIELD_NAME>
	</FIELD>
	<FIELD>
		<XML_FIELD_NAME>Quantity</XML_FIELD_NAME>
		<XML_FIELD_CONTEXT>dataroot/Order_x0020_Details/Quantity</XML_FIELD_CONTEXT>
		<IDEA_FIELD_NAME>QUANTITY</IDEA_FIELD_NAME>
	</FIELD>
	<FIELD>
		<XML_FIELD_NAME>UnitPrice</XML_FIELD_NAME>
		<XML_FIELD_CONTEXT>dataroot/Order_x0020_Details/UnitPrice</XML_FIELD_CONTEXT>
		<IDEA_FIELD_NAME>UNITPRICE</IDEA_FIELD_NAME>
	</FIELD>
	<FIELD>
		<XML_FIELD_NAME>ProductID</XML_FIELD_NAME>
		<XML_FIELD_CONTEXT>dataroot/Order_x0020_Details/ProductID</XML_FIELD_CONTEXT>
		<IDEA_FIELD_NAME>PRODUCTID</IDEA_FIELD_NAME>
	</FIELD>
	<FIELD>
		<XML_FIELD_NAME>OrderID</XML_FIELD_NAME>
		<XML_FIELD_CONTEXT>dataroot/Order_x0020_Details/OrderID</XML_FIELD_CONTEXT>
		<IDEA_FIELD_NAME>ORDERID</IDEA_FIELD_NAME>
	</FIELD>
	<FIELD>
		<XML_FIELD_NAME>noNamespaceSchemaLocation</XML_FIELD_NAME>
		<XML_FIELD_CONTEXT>dataroot/noNamespaceSchemaLocation</XML_FIELD_CONTEXT>
		<IDEA_FIELD_NAME>NONAMESPACESCHEMALOCATION</IDEA_FIELD_NAME>
	</FIELD>
</IDEA_IMPORT_FIELDS>

 

mohamed Mon, 07/10/2017 - 05:39

Hi Brian,
I am building an xrdf file for importing to Idea. One of the fields is a boolean type. How do I specify the field type in the xrdf file? Other field types are imported properly based on the value. But boolean field is not imported properly. It's added as text field. Could you suggest any solution for this?
 
Thanks,
Shafeer

Brian Element Mon, 07/10/2017 - 19:27

Hi Shafeer,

 I haven't done much with xml in the past but looking at it, it looks like the import is like Excel where you can't really define how IDEA will bring in the field, you have to accept what IDEA decides.  Your best bet is to create a virtual field and change your field into boolean that way.  You could develop a script that imports the file and then adds the boolean field automatically.

Brian

mohamed Tue, 07/11/2017 - 00:55

Thanks Brian for your suggestion.
My code is already appending boolean field. But want to remove that way as the equation used for appending field is failing when the list separator and decimal separator set in the locale setting are same. User has set comma for both list separator and decimal separator in the control panel. Hope you remember the discussion about this issue sometime back. http://ideascripting.com/comment/1447#comment-1447
Thanks,
Shafeer
 

Brian Element Tue, 07/11/2017 - 05:13

Hi Shafeer,

Could you share part of that file, you can email it directly to me at brian.element@ideascripting.com.  I am just wondering if a custom function couldn't be created to get around the problem with the decimal.  Another thought would be to create an equation, change the number to text, remove the commas and then change it back to numeric and divide by 100?  Would that work to get it in the proper format?

Thanks

Brian

mohamed Thu, 07/13/2017 - 06:19

Hi Brian,
 
Thanks for your suggestion. I have resolved the issue without using @IF function of Idea. We are already loading the database into .net data table object. So, I could manage to add an integer field in the datatable based on values in the boolean field. Then new field will have 1 or 0. This data table table is exported to xml and the xml is imported to Idea. So I will have a numeric field in the Idea database with values 1 and 0. Then I appended a boolean field on the database by giving the numeric column in the equation.
 
PS: My code was already having steps for keeping data in datatable, export to xml and import xml to Idea. So I just had to add few more steps. 
 
Thanks,
Shafeer

mrsjagirard Thu, 08/06/2020 - 12:00

Hi Brian- I was using your script for importing multiple files but got hung up on this same area- creating a record definition. Your instructions say "For the Print Report, EBCDIC, Text Fixed Length, Text Delimited and XML you will first have to create a record definition for the files that you import." I am importing XML but I have no idea what a record definition is or how to create it. I've googled and can't find the answer. Could you help? Thanks.