SSDN - Exporting XML to a relational database

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Rodney Rau

Subject: Exporting XML to a relational database
Author: Rodney Rau
Date: 06 Sep 2005 06:01 PM

I am brand new with xml and Stylus Studio. Can I import an xml file into Stylus Studio and export the data to a file format such as tab or comma delimited so I can import it via dts into ms sql server? Is xquery the way to head?

Tony Lavinio

Subject: Exporting XML to a relational database
Author: Tony Lavinio
Date: 07 Sep 2005 01:34 AM

Tab or CSV files are easy to create with either XSLT or XQuery from
XML input files. If you post your sample file, we can try to help,
or you can ask on the xsl-list email list.

Ivan Pedruzzi

Subject: Exporting XML to a relational database
Author: Ivan Pedruzzi
Date: 07 Sep 2005 12:32 PM

Rodney,

The best way to bulk load a XML file into a single relational table is to use the Stylus Studio run-time library

1) create a DB to XML Data Source:

- File -> New -> DB to XML Data Source
- Fill the scenario with the connection information
- drag & drop the table you want to map from the database schema tree into the editor and pick SELECT (SQL/XML)
- *switch to the UPDATE tab* and drag & drop the table you want to map from the database schema tree into the editor and pick INSERT
- File -> Save as loader.rdbxml

2) Create a XSLT to map your XML document into the database table

The relation table needs to contain at least few records to be use in mapper.

- File -> New -> XSLT Mapper
- click on "Set target Document" then click on "Relation DB" the select loader.rdbxml
You will see the tree populated with the XML View of your table

- click on "Add Source Document" and pick your XML file
- map the elements you want to copy over
- click on the "..." toolbar button and set the Output URL to db:///loader.rdbxml then click OK

Now when you click the green triangle the XSLT processor will translate your XML document into a document compatible with XML view then the adapter will execute SQL INSERT for each XML node you have mapped.

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

Julie Green

Subject: Exporting XML to a relational database
Author: Julie Green
Date: 11 Oct 2005 01:50 PM

I followed all of your instructions, but clicking the "green arrow" only gets me a preview of the data in the transformed XML. How to I take the next step and have that XML saved to the corresponding table? I.e. my insert statement is NOT happening, so there must be another step to get the "adapter" to execute the Insert statement. This is my first try at using Stylus Studio, and I'm a newbie to XML in general as well, so this is probably something obvious that I just don't get. Thanks for your help!

Julie Green

Subject: Exporting XML to a relational database
Author: Julie Green
Date: 11 Oct 2005 02:20 PM

I found my error. When defining the output file, I manually typed in my relational database datasource. When I went back to that dialog box, and selected it using the "..." button, it prompted me with whether or not to overwrite the existing datasource. I said "yes", then the next time I clicked the green triangle (Previewing results of the transformation), the Insert statement ran and the data from my XML file was inserted into the database. Incidentally for those who are as clueless as I was, I also found (in the documentation) that the INSERT statement of the datasource is only performed when the datasource is saved. This is why I had to say "yes" to overwriting existing files, so that when the transformation runs, it saves the datasource, thus triggering the INSERT statement. Not very intuitive, but it works.

(Deleted User)

Subject: Exporting XML to a relational database
Author: (Deleted User)
Date: 11 Oct 2005 02:46 PM

Most likely your output URL is not set in the scenario:

click "..." toolbar button
you will get "Scenario Properties" dialog

make sure that "Output URL (optional):" contains "db:///loader.rdbxml"

Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.