I have a XML file that is giving MS Access 2010 fits when using the vba.ImportXML function.There are two problems:Too many child levels as each creates a new table.Some fields are too long and cause a failed import. With a manual import, a table called "import errors" is created, but with the VBA.ImportXML fuction it just fails.Problem #1 Here is a sample of what the XML looks like and what I need to be.Current XML<root><Providers> <ProviderID>209834750928473509827345098723</ProviderID> <FirstName>Robert</FirstName> <MiddleName>Clarence</MiddleName> <LastName>Dude</LastName> <Suffix/> <Salutation/> <PreferredName/> <Degree1> <Code>MD</Code> <Description>MD (MD)</Description> </Degree1> <Specialty1> <Code>32</Code> <Description>Obstetrics/Gynecology (32)</Description> </Specialty1></Providers><Providers> <ProviderID>297502983475092374059827435</ProviderID> <FirstName>John</FirstName> <MiddleName/> <LastName>Doe</LastName> <Suffix>Jr</Suffix> <Salutation/> <PreferredName/> <Degree1> <Code>PhD</Code> <Description>PhD (Phd)</Description> </Degree1> <Specialty1> <Code>44</Code> <Description>General Prac</Description> </Specialty1></Providers></root>Notice the nodes at Degree1, Specialty1, etc. This is just a small sample as there can be hundreds of child nodes (and thousands of Providers) and may be more than one level deeper than the base data. Each needs to be collapsed so that it's name is a combination of the parent and child names. For example:<Degree1> <Code>PhD</Code> <Description>PhD (Phd)</Description></Degree1><Specialty1> <Code>44</Code> <Description>General Prac</Description></Specialty1>Would become:<Degree1_Code>PhD</Degree_Code><Degree1_Description>PhD (Phd)</Degree1_Description><Specialty1_Code>44</Specialty1_Code><Specialty1_Description>General Prac</Specialty1_Description>The names of the child nodes are not always the same as "Code" and "Description", so if this can be done on a generic level, all the better.The final output should look like this:<root><Providers> <ProviderID>209834750928473509827345098723</ProviderID> <FirstName>Robert</FirstName> <MiddleName>Clarence</MiddleName> <LastName>Dude</LastName> <Suffix/> <Salutation/> <PreferredName/> <Degree1_Code>MD</Degree1_Code> <Degree1_Description>MD (MD)</Degree1_Description> <Specialty1_Code>32</Specialty1_Code> <Specialty1_Description>Obstetrics/Gynecology (32)</Specialty1_Description> </Specialty1></Providers><Providers> <ProviderID>297502983475092374059827435</ProviderID> <FirstName>John</FirstName> <MiddleName/> <LastName>Doe</LastName> <Suffix>Jr</Suffix> <Salutation/> <PreferredName/> <Degree1_Code>PhD</Degree1_Code> <Degree1_Description>PhD (Phd)</Degree1_Description> <Specialty1_Code>44</Specialty1_Code> <Specialty1_Description>General Prac</Specialty1_Description></Providers></root>Problem #2How can I limit the size of a field "Comments" to just the left 255 characters?btw, I will be using this method to transform the XML prior to ImportXML:http://www.oreillynet.com/xml/blog/2005/04/transforming_xml_in_microsoft.htmlThanks in advance.----Update 20120613 ---------------I found a partial answer thanks to Alex Nikolaenkov at this post:Hierarchial xml to Flat xml using XSLT<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:strip-space elements="*"/><xsl
utput indent="yes" method="xml"/><xsl:template match="root"> <xsl:copy> <xsl:apply-templates select="*" mode="flatten"/> </xsl:copy></xsl:template><xsl:template match="*[normalize-space(text())]" mode="flatten"> <xsl
aram name="name-prefix" select="''"/> <xsl:variable name="name"> <xsl:call-template name="construct-name"> <xsl:with-param name="name-prefix" select="$name-prefix"/> </xsl:call-template> </xsl:variable> <xsl:element name="{$name}"> <xsl:apply-templates select="text()"/> </xsl:element> <xsl:apply-templates select="node()" mode="flatten"> <xsl:with-param name="name-prefix" select="$name"/> </xsl:apply-templates></xsl:template><xsl:template match="*[not(normalize-space(text()))]" mode="flatten"> <xslaram name="name-prefix" select="''"/> <xsl:variable name="prefix"> <xsl:call-template name="construct-name"> <xsl:with-param name="name-prefix" select="$name-prefix"/> </xsl:call-template> </xsl:variable> <xsl:apply-templates select="node()" mode="flatten"> <xsl:with-param name="name-prefix" select="$prefix"/> </xsl:apply-templates></xsl:template><xsl:template name="construct-name"> <xslaram name="name-prefix"/> <xsl:choose> <xsl:when test="$name-prefix"> <xsl:value-of select="concat($name-prefix, '-', local-name(.))"/> </xsl:when> <xsltherwise> <xsl:value-of select="local-name(.)"/> </xsltherwise> </xsl:choose></xsl:template><xsl:template match="text()" mode="flatten"/></xsl:stylesheet>The only problem is that it collapses all the nodes except Root while I need to leave "Providers" alone.Here is a sample of his XSL on my data.<root><Providers-ProviderID>7fa4611a-c7bb-4751-a81b-0e41dd7d448e</Providers-ProviderID><Providers-SSN>517-68-3834</Providers-SSN><Providers-FirstName>Robert</Providers-FirstName><Providers-LastName>Aagard</Providers-LastName><Providers-Degree1-Code>MD</Providers-Degree1-Code><Providers-Degree1-Description>MD (MD)</Providers-Degree1-Description><Providers-Specialty1-Code>32</Providers-Specialty1-Code><Providers-Specialty1-Description>Obstetrics/Gynecology (32)</Providers-Specialty1-Description><Providers-Gender>M</Providers-Gender><Providers-BirthDate>1961-10-06T00:00:00-07:00</Providers-BirthDate><Providers-Graduate>0001-01-01T00:00:00-08:00</Providers-Graduate><Providers-TaxID>262072378</Providers-TaxID><Providers-OtherID>582418</Providers-OtherID><Providers-AcceptNewPatient>true</Providers-AcceptNewPatient><Providers-AcceptMedicare>false</Providers-AcceptMedicare><Providers-AcceptMedicaid>false</Providers-AcceptMedicaid>There is also the issue of my Problem #2, limiting the data in fields called "Comments" to just the first 255 charecters.
utput indent="yes" method="xml"/><xsl:template match="root"> <xsl:copy> <xsl:apply-templates select="*" mode="flatten"/> </xsl:copy></xsl:template><xsl:template match="*[normalize-space(text())]" mode="flatten"> <xslaram name="name-prefix" select="''"/> <xsl:variable name="name"> <xsl:call-template name="construct-name"> <xsl:with-param name="name-prefix" select="$name-prefix"/> </xsl:call-template> </xsl:variable> <xsl:element name="{$name}"> <xsl:apply-templates select="text()"/> </xsl:element> <xsl:apply-templates select="node()" mode="flatten"> <xsl:with-param name="name-prefix" select="$name"/> </xsl:apply-templates></xsl:template><xsl:template match="*[not(normalize-space(text()))]" mode="flatten"> <xslaram name="name-prefix" select="''"/> <xsl:variable name="prefix"> <xsl:call-template name="construct-name"> <xsl:with-param name="name-prefix" select="$name-prefix"/> </xsl:call-template> </xsl:variable> <xsl:apply-templates select="node()" mode="flatten"> <xsl:with-param name="name-prefix" select="$prefix"/> </xsl:apply-templates></xsl:template><xsl:template name="construct-name"> <xslaram name="name-prefix"/> <xsl:choose> <xsl:when test="$name-prefix"> <xsl:value-of select="concat($name-prefix, '-', local-name(.))"/> </xsl:when> <xsltherwise> <xsl:value-of select="local-name(.)"/> </xsltherwise> </xsl:choose></xsl:template><xsl:template match="text()" mode="flatten"/></xsl:stylesheet>The only problem is that it collapses all the nodes except Root while I need to leave "Providers" alone.Here is a sample of his XSL on my data.<root><Providers-ProviderID>7fa4611a-c7bb-4751-a81b-0e41dd7d448e</Providers-ProviderID><Providers-SSN>517-68-3834</Providers-SSN><Providers-FirstName>Robert</Providers-FirstName><Providers-LastName>Aagard</Providers-LastName><Providers-Degree1-Code>MD</Providers-Degree1-Code><Providers-Degree1-Description>MD (MD)</Providers-Degree1-Description><Providers-Specialty1-Code>32</Providers-Specialty1-Code><Providers-Specialty1-Description>Obstetrics/Gynecology (32)</Providers-Specialty1-Description><Providers-Gender>M</Providers-Gender><Providers-BirthDate>1961-10-06T00:00:00-07:00</Providers-BirthDate><Providers-Graduate>0001-01-01T00:00:00-08:00</Providers-Graduate><Providers-TaxID>262072378</Providers-TaxID><Providers-OtherID>582418</Providers-OtherID><Providers-AcceptNewPatient>true</Providers-AcceptNewPatient><Providers-AcceptMedicare>false</Providers-AcceptMedicare><Providers-AcceptMedicaid>false</Providers-AcceptMedicaid>There is also the issue of my Problem #2, limiting the data in fields called "Comments" to just the first 255 charecters.