match data to schema

[lozkane]

I'm creating an xml to import into Indesign and have data in an excel doc - named to match a schema file for the Indesign structure.
Can you tell me how I can match these up in Oxygen?

[adrian]

Hello,

This is a manual process in Oxygen.
You can either:

1. Import the Excel file in Oxygen(File -> Import -> MS Excel File). You can adjust the element names for rows and columns under "Change labels".
Afterwards you can process the imported XML file either manually in Text or Grid mode or with a stylesheet that rearranges the data in conformance with the schema.

2. Create a skeleton document starting from the schema, add at least two records(data rows) in it. Switch to grid mode(Document -> Edit Mode -> Grid) and expand the grid model to see the records in a tabular form. Open the Excel document in MS Excel or OpenOffice Calc and there copy columns of data(one by one or groups of adjacent columns) and paste them in the Oxygen grid.

Regards,
Adrian

[lozkane]

Thanks for the response. I'm trying the first method, could you explain how to create a stylesheet that arranges the data as per schema? Appreciate your ideas!

[adrian]

You can manually write an XSLT stylesheet(or an XQuery) that does this for you, there's no automatic code generation. You do need a bit of XSLT(or XQuery) knowledge, but Oxygen does have some helpers for this.

Actually, I find XQuery to be more straightforward in these situations.

Create a new XQuery file(File -> New -> XQuery), create a new transformation scenario for it(Document -> Transformation -> Configure Transformation Scenario, New), in the XML URL choose the input XML file. Now if you look in the "XSLT/XQuery input" view(Window -> Show View -> "XSLT/XQuery input" - last) you will see the structure of the XML document. From this structure you can drag and drop to create certain XQuery structures.

It's generally a good idea to start the XQuery from the desired XML target(exactly what you want to obtain as XML) and start replacing the text between element tags with the XQuery code that extracts the data from the source.
e.g.
Source XML:

Code: Select all

<library>

    <book>

        <title>The Green Mile</title>

        <author>King, Stephen</author>

        <price>18</price>

    </book>

</library>

Desired XML:

Code: Select all

<Records>

   <Record>

      <title>The Green Mile</title>

      <author>

         <first-name>Stephen</first-name>

         <last-name>King</last-name>

      </author>

   </Record>

</Records>

XQuery:

Code: Select all

<Records>

    {for $book in /library/book

    let $tokenizedAuthor := tokenize($book/author/text(), ',')

    let $lastName := normalize-space($tokenizedAuthor[1])

    let $firstName := normalize-space($tokenizedAuthor[2])

    return

    <Record>

        <title>{$book/title/text()}</title>

        <author>

           <first-name>{$firstName}</first-name>

           <last-name>{$lastName}</last-name>

        </author>

    </Record>}

</Records>

Regards,
Adrian

[lozkane]

Great, thanks for your help Adrian. I'll work on this.

[lozkane]

I had a Xquery transformation scenario working but have lost track now of how I had it all setup.
In 'edit transformation scenario' I would like to select the currently open xml (which I would have just imported from xls) as the 'XML URL'.
Is there an editor variable I can insert here to choose that xml to transform. I'm pretty sure that's what was happening earlier when it all just worked!

[lozkane]

One more thing, when I import my xls the date format is in joolean with time, and some number have 10 decimal places. Is there a way to change to normal date format and say 2 decimal places in the transformation (or in the import)?

[sorin_ristache]

Hello

I had a Xquery transformation scenario working but have lost track now of how I had it all setup.

If you pressed the OK button instead of Cancel in the dialog for editing a scenario then the scenario should still be present in the list of scenarios. Just select it in the list and press the Edit button.

In 'edit transformation scenario' I would like to select the currently open xml (which I would have just imported from xls) as the 'XML URL'.
Is there an editor variable I can insert here to choose that xml to transform. I'm pretty sure that's what was happening earlier when it all just worked!

At the right of the XML URL combo box there is a button that displays a list of the editor variables. The variable for the current XML file is ${currentFileURL}.


Regards,
Sorin

[sorin_ristache]

One more thing, when I import my xls the date format is in joolean with time, and some number have 10 decimal places. Is there a way to change to normal date format and say 2 decimal places in the transformation (or in the import)?

You can change the date format in the imported document (the document created with the action from menu File -> Import -> MS Excel File) from menu Options -> Preferences -> XML -> Import -- Date/Time format -- Custom format.


Regards,
Sorin

[delta68]

Hi,

I would like to do the same thing. I'm a complete newbie so please bare with me.

1) I have a supplied schema that I want use to create an xml file from data I have in excel. I want to create a skelton document as mentioned above, from the schema and then copy and paste from excel. I have had no luck in creating this "skelton" file. Can you supply a step by step? I searched the documentation... but have had no luck.

Thanks in advance.

[adrian]

Hello,

The simplest way to create a skeleton document is to generate a sample XML file from the supplied XML schema(Tools -> Generate Sample XML Files).
Browse for the schema and pick the proper root element.
If you don't know what root element to pick, you should first open the XML schema to get some idea of how it is structured.

After generating and opening the sample XML file, you can switch to Grid mode(Document -> Edit mode -> Grid) and copy from Excel and paste into the grid.

Regards,
Adrian

[delta68]

Thanks Adrian! That really helped!