SSDN - Populating MS access from XML datafiles

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

John Redpath

Subject: Populating MS access from XML datafiles
Author: John Redpath
Date: 23 Jan 2006 04:04 PM

I have XML data files that we receive from an outside entity. I want to be able to populate the data into an MS-Access database. It doesn't seem like there is a good import capability in Access to do this. I see that your software apparently has the ability to create schemas and it seems that is has some type of mapping capability. Am I looking at the right software to do this ?

Tony Lavinio

Subject: Populating MS access from XML datafiles
Author: Tony Lavinio
Date: 23 Jan 2006 04:35 PM

Stylus Studio can certainly be used to update an Access database
from XML. For a pointer directly into the documentation, see
http://www.stylusstudio.com/docs/v2006/d_rdb35.html for the
documentation.

http://www.stylusstudio.com/xml_database.html has more information.
Since we provide the DataDirect Technologies SequelLink product,
your SQL code will run unchanged against Access, SQL Server, Oracle,
Sybase or any other database you need to run it against in the future.

Susie Shaffer

Subject: Populating MS access from XML datafiles
Author: Susie Shaffer
Date: 06 Mar 2006 04:17 PM

Hi,

I need to import XML data into an Access Database. Access requires the XML to be element based, but the file I am working with is Attribute-based. It seems to me that I can create an element-based xml file using style sheets, and I was hoping your software could help me build a style sheet.

However, someone posted a similar question to mine and your response was:
"Stylus Studio can certainly be used to update an Access database from XML. For a pointer directly into the documentation, see
http://www.stylusstudio.com/docs/v2006/d_rdb35.html for the
documentation"

This link takes you to DB-to-XML documentation. I need XML-to-DB documentation.

Can you tell me the best way to proceed?

(Deleted User)

Subject: Populating MS access from XML datafiles
Author: (Deleted User)
Date: 06 Mar 2006 04:43 PM

Hi, Susie. In order to write back to the database (which is what I'm assuming you need/mean when you ask about XML-to-DB functionality), you need to define the appropriate SQL code on the UPDATE tab of the DB-to-XML Editor. When you save a DB-to-XML data source you have opened as XML in Stylus Studio, any UPDATE/INSERT actions you have defined are performed at that time.

A short video illustrating this procedure is described in the following thread.

http://www.stylusstudio.com/SSDN/default.asp?action=9&read=4754&fid=23

You will need to use DataDirect SequeLink Server to configure MS Access as a data source, as described here:

http://www.stylusstudio.com/configure_data_source.html

Hope this helps.

David Foster
Stylus Studio Team

Susie Shaffer

Subject: Populating MS access from XML datafiles
Author: Susie Shaffer
Date: 06 Mar 2006 06:09 PM

Ok, thanks for your response.

Couldn't I also handle it this way.

Access will NOT import attribute-centric XML files. However, Access WILL allow you to import an element-centric XML files.

The Microsoft websites gives an example of creating an attribute-centric file from the sample Northwinds file. It then takes the attribute-centric XML file, applys a style sheet to create an element-centric XML file. It then imports the element-centric XML file into a table.

I have an attribue-centric file that I would like to bring into Access. So I thought, if I create a style sheet, I could create a new element-centric XML file and import it into Access.

That's where stylusstudio comes in. Can I use stylusstudio to create a style sheet for me. I have the source code (an attribue-centric XML file) and it references an xsd file.

Thanks

Minollo I.

Subject: Populating MS access from XML datafiles
Author: Minollo I.
Date: 06 Mar 2006 08:39 PM

Susie,
you may want to post your "attribute-centric" XML document for specific help; this XSLT will in general convert attributes into child elements for any XML...

<xsl:template match="/">
<xsl:apply-templates/>
</xsl:template>
<xsl:template match="@*">
<xsl:element name="{name()}">
<xsl:value-of select="."/>
</xsl:element>
</xsl:template>
<xsl:template match="*">
<xsl:element name="{name()}">
<xsl:apply-templates select="@*"/>
<xsl:apply-templates/>
</xsl:element>
</xsl:template>

Susie Shaffer

Subject: Populating MS access from XML datafiles
Author: Susie Shaffer
Date: 07 Mar 2006 08:11 AM

OK, I will give your sample a try.

Meanwhile, the file I am trying to change is:

http://nvd.nist.gov/download/nvdcve-modified.xml

Minollo I.

Subject: Populating MS access from XML datafiles
Author: Minollo I.
Date: 07 Mar 2006 09:12 AM

Taking a quick look at the XML file you attached, I have the impression that you want to control more carefully what and how is stored in your database... which means the way to go is probably what David mentioned yesterday.

Hope this helps,
Minollo

McKyle Adams

Subject: Populating MS access from XML datafiles
Author: McKyle Adams
Date: 11 Jun 2006 05:43 PM

Susie,

Did you ever get a response on this that worked? I have the same issue and have yet to figure out how to do this conversion to get the data into Access.

Thanks,

McKyle

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.