match data to schema

Questions about XML that are not covered by the other forums should go here.
lozkane
Posts: 5
Joined: Tue Oct 19, 2010 2:15 am

match data to schema

Post by lozkane » Tue Oct 19, 2010 3:16 am

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
Posts: 2548
Joined: Tue May 17, 2005 4:01 pm

Re: match data to schema

Post by adrian » Tue Oct 19, 2010 10:35 am

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
Adrian Buza
<oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger
http://www.oxygenxml.com

lozkane
Posts: 5
Joined: Tue Oct 19, 2010 2:15 am

Re: match data to schema

Post by lozkane » Tue Oct 19, 2010 12:38 pm

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
Posts: 2548
Joined: Tue May 17, 2005 4:01 pm

Re: match data to schema

Post by adrian » Tue Oct 19, 2010 3:37 pm

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
Adrian Buza
<oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger
http://www.oxygenxml.com

lozkane
Posts: 5
Joined: Tue Oct 19, 2010 2:15 am

Re: match data to schema

Post by lozkane » Wed Oct 20, 2010 12:14 am

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

lozkane
Posts: 5
Joined: Tue Oct 19, 2010 2:15 am

Re: match data to schema

Post by lozkane » Tue Jan 18, 2011 2:20 am

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
Posts: 5
Joined: Tue Oct 19, 2010 2:15 am

Re: match data to schema

Post by lozkane » Tue Jan 18, 2011 2:23 am

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
Posts: 4144
Joined: Fri Mar 28, 2003 2:12 pm

Re: match data to schema

Post by sorin_ristache » Wed Jan 19, 2011 12:11 pm

Hello
lozkane wrote: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.
lozkane wrote: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
Posts: 4144
Joined: Fri Mar 28, 2003 2:12 pm

Re: match data to schema

Post by sorin_ristache » Wed Jan 19, 2011 12:15 pm

lozkane wrote: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
Posts: 2
Joined: Tue Jan 25, 2011 9:14 pm

Re: match data to schema

Post by delta68 » Tue Jan 25, 2011 9:55 pm

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
Posts: 2548
Joined: Tue May 17, 2005 4:01 pm

Re: match data to schema

Post by adrian » Wed Jan 26, 2011 6:36 pm

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
Adrian Buza
<oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger
http://www.oxygenxml.com

delta68
Posts: 2
Joined: Tue Jan 25, 2011 9:14 pm

Re: match data to schema

Post by delta68 » Wed Feb 02, 2011 7:56 pm

Thanks Adrian! That really helped!

Post Reply