XML into Access 2007
Confused
Access 2007 database. I can 'Import XML File' but in the 'Import XML' window,
only the column 'ID' appears. When I click ok the table remains blank.
How do I get Access to understand how to display the info? I have never come
across XML before and so need very basic instructions.
Thanks so much.
Peter Jamieson
necessary) of your XML?
One possibility is that your XML is "attribute-centric" rather than
"element-centric". e.g. Access should import stuff that is defined using
elements (<element-name>data value</element-name> like this:
<?xml version='1.0' encoding='UTF-8'?>
<dataroot>
<sample>
<ID>1</ID>
<text1>v1</value1>
<text2>v2</value2>
</sample>
</dataroot>
or even this
<?xml version='1.0' encoding='UTF-8'?>
<sample>
<ID>1</ID>
<text1>v1</value1>
<text2>v2</value2>
</sample>
but it will not cope with "attribute-centric" XML, e.g. like this:
<?xml version='1.0' encoding='UTF-8'?>
<sample>
<ID value="1" text1="v1" text2="v2" />
</sample>
Some applications generate "attribute-centric" XML as standard, for
example there is a commonly-used method that saves an ADO recordset with
a "table" called data whose elements are "attribute-centric" XML with
elements called "row", e.g. with each record looking something like this:
<row ID='1' Company='Company A' c2='Bedecs' c3='Anna' c5='Owner'
c6='(123)555-0100' c9='(123)555-0101' Address='123 1st Street'
City='Seattle' c12='WA' c13='99999' c14='USA' Attachments=''/>
In general, to resolve this problem you need either to
a. get people to send XML that you /can/ import or
b. transform the XML before you try to import it.
Without knowing the situation it would be difficult to know which would
be the best approach, but (a) would obviously be easier for you.
Otherwise, if you have simple "attribute-centric" XML it would probably
be reasonably easy to adapt the transform provided in the following
rather techie article.
http://support.microsoft.com/kb/285329
If you need to go step-by-step through that, post back. But we need to
know more about what XML format(s) you have to deal with etc.
Peter Jamieson
Confused
Thanks very much for your reply. I have been given an example purchase order
by the company which is about 3 A4 pages long. From what you say it does
appear to be 'attribute-centric' and unfortunatly getting them to change this
will not be an option. This is the begining bit (in the interested of
confideniality, I have replaced any mention of the company name with
[company]):
<cXML payloadID="1234@[company].co.uk" timestamp="2008-06-03T15:04:27"
xml:lang="en">
<Header>
<From>
<Credential domain="[company]Organisation">
<Identity>[company].</Identity>
</Credential>
</From>
<To>
<Credential domain="[company]Organisation">
<Identity>[company]-123456-DC</Identity>
</Credential>
</To>
<Sender>
<Credential domain="[company]Organisation">
<Identity>ECOMMERA</Identity>
</Credential>
<UserAgent>ALSB</UserAgent>
</Sender>
</Header>
<Request deploymentMode="production">
<OrderRequest>
<OrderRequestHeader orderID="1234"
orderDate="2008-06-03T15:01:42" type="new">
<BillTo>
<Address>
<Name xml:lang="en">[company]</Name>
<PostalAddress>
<DeliverTo>Mr John Smith</DeliverTo>
<Street>3 New Road</Street>
<City>Slough</City>
<State>Berkshire</State>
<PostalCode>SL1 2AB</PostalCode>
<Country isoCountryCode="GB">GB</Country>
</PostalAddress>
</Address>
</BillTo>
Confused
I just tried to follow the instructions for importing attribute-centric XML
documents, copy and pasting the code, but when I pasted into the 'Immediate'
view and hit enter I get a error saying "User-defined type not defined".
Am I meant to be editing the code as well as pasting it?
Peter Jamieson
"attribute-centric") that Access cannot cope with without a transform.
The transform provided in the example is probably not going to work.
I'm not too quick on constructing transforms but will try to get back to
you on this by tomorrow, assuming no-one else jumps in with The Right
Answer.
Are all the structures you have to import the same as this one, or are
you dealing with a number of different possibilities?
Peter Jamieson
Peter Jamieson
would be able to do it much better. It wouldn't take much to make it
fail, but it works with the sample data you provided.
The problem that you have with this type of XML is that the same element
names are re-used in different contexts - e.g. in this case there are
three "leaf nodes", each called Identity. To import your data
successfully, you really need each element to have a distinct name, or
you need to import several tables with known relationships, or some
combination of the two. In this case, the transform tries to provide
each attribute and element with a unique name built from the names of
the enclosing elements. So for example the three Identity elements are
given the following names:
He_Fr_Cr_Identity
He_To_Cr_Identity
He_Se_Cr_Identity
Things would be slightly more obvious if the various parts of the names
were spelled out in full, but then the names might well be too long for
Access (I don't know its limitations in this respect). But you can
probably work out how to make simple fixes, e.g. use the first three
letters of each element, rather than just 2.
In essence, the transform looks for all the elements that have
attributes, then creates a new element for each attribute. (There are
some "xml:lang" attributes in there - I strip out the "xml:", but this
means that attributes with the same name but different namespaces will
end up with the same field name, so you may need to modify that).
Then it looks for all "leaf" elements - i.e. elements with no child
elements - and outputs them with new names.
If you happen to have any elements that contain data other than child
elements, the transform would need to be changed to reflect that.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
exclude-result-prefixes="msxsl"
>
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<!-- root element for the XML output -->
<dataroot>
<!-- for each cXML element -->
<xsl:for-each select="/cXML">
<!--
This will be used for the table name imported into Access.
Change this name (and the /payload lower down)
to suit your needs.
-->
<payload>
<!--
for each element that has attributes
-->
<xsl:for-each
select="descendant-or-self::*[count(attribute::*) > 0]">
<!--
make a name for the element
-->
<xsl:variable name="fieldnamestem">
<xsl:call-template name="make-fieldnamestem-from-element">
<xsl:with-param name="path"
select="node()"/>
</xsl:call-template></xsl:variable>
<!--
for each attribute in that element
-->
<xsl:for-each select="@*">
<xsl:element name="{concat($fieldnamestem,local-name())}">
<xsl:value-of select="."/>
</xsl:element>
</xsl:for-each>
</xsl:for-each>
<!--
for each leaf element
-->
<xsl:for-each select="//*[count(child::*) = 0]">
<xsl:variable name="fieldname">
<xsl:call-template name="make-fieldname-from-element">
<xsl:with-param name="path"
select="node()"/>
</xsl:call-template>
</xsl:variable>
<xsl:element name="{$fieldname}">
<xsl:value-of select="."/>
</xsl:element>
</xsl:for-each>
</payload>
</xsl:for-each>
</dataroot>
</xsl:template>
<xsl:template name="make-fieldname-from-element">
<xsl:param name="path"/>
<xsl:for-each select="$path/ancestor::*[not(name()=name(/*) or
name()=name($path/..))]">
<xsl:value-of select="concat(substring(name(),1,2),'_')"/>
</xsl:for-each>
<xsl:value-of select="name()"/>
</xsl:template>
<xsl:template name="make-fieldnamestem-from-element">
<xsl:param name="path"/>
<xsl:for-each select="$path/ancestor::*[not(name()=name(/*))]">
<xsl:value-of select="concat(substring(name(),1,2),'_')"/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Peter Jamieson