Importing a XML file in MS Access

2019-07-31 10:34发布

站内文章 / 前沿技术
5 0 0

问题:

I'm trying to create a XSLT file (XML transformation file) to import this XML in MS Access, can anyone help me?

I don't know stylesheet, I tried to create one but it doesn't show me the QUOTATION NUMBER and DATE values in each imported table as I need...

<<<< NEW XML FILE >>>>

<?xml version="1.0" encoding="UTF-8"?>
<BRAND_QUOTES xmlns:xalan="http://xml.apache.org/xalan" xmlns:java="http://xml.apache.org/xslt/java" xmlns:brand="http://brand" RECEIVER_ID="0000112233" SENDER_ID="888800000008">
  <QUOTATION NUMBER="1919999999" DATE="20170208" TREATMENT="Back Margin" DIRECT_BACK_REBATE_APPLY="TRUE">
    <PARTY_DETAILS ROLE="SOLD_TO">
      <ID TYPE="GLN">0000119379</ID>
      <CONTACT_DETAILS>
        <NAME>TDC SPA</NAME>
        <STREET>R. PERSATTE</STREET>
        <COUNTRY>FR</COUNTRY>
        <POSTCODE>25687</POSTCODE>
        <PHONE>002996644</PHONE>
      </CONTACT_DETAILS>
    </PARTY_DETAILS>
    <PARTY_DETAILS ROLE="INDIRECT_CUSTOMER">
      <CONTACT_DETAILS>
        <NAME>JEANPIERRE PORRET</NAME>
        <VAT>FR00256893364</VAT>
      </CONTACT_DETAILS>
    </PARTY_DETAILS>
    <PARTY_DETAILS ROLE="SALES_EMPLOYEE">
      <CONTACT_DETAILS>
        <NAME>Marta Rossi</NAME>
      </CONTACT_DETAILS>
    </PARTY_DETAILS>
    <PARTY_DETAILS ROLE="FINAL_CUSTOMER">
      <CONTACT_DETAILS>
        <NAME>CRISTIANO MOHAMED</NAME>
      </CONTACT_DETAILS>
    </PARTY_DETAILS>
    <DATE TYPE="VALID_FROM">20161103</DATE>
    <DATE TYPE="VALID_TO">20201231</DATE>
    <LEGAL_TEXTS>
      <TEXT TYPE="FIXED_TEXT1">Faisant suite à notre dernier entretien,nous avons le plaisir de vous transmettre ci-dessous nos conditions pour cette affaire</TEXT>
      <TEXT TYPE="AUTO_TEXT">Le chiffre d'affaire réalisé sur cette cotation ne sera pas intégrédans  la base de calcul des bonus contractuels.</TEXT>
      <TEXT TYPE="FIXED_TEXT2">Cette offre est valable dans la limite des stocks disponibles et sous réserve de modification de prix de notre part, aux Conditions deréglements contractuelles, Nous vous prions d'identifier vos commandesavec la mention "Affaires Spéciales" en reportant le numéro decotation et le code marché et de joindre les justificatifs descommandes clients ,Nous souhaitons que ces conditions vous permettentde donner une suite favorable à cette affaire, Dans l'attente , nousvous prions de croire en l'assurance de notre considérationdistinguée.</TEXT>
      <TEXT TYPE="HEADER_TEXT" />
    </LEGAL_TEXTS>
    <LINE_ITEMS>
      <ITEM>
        <PRODUCT_CODE TYPE="BRAND">C11CD47301</PRODUCT_CODE>
        <PRODUCT_CODE TYPE="EAN">8715946552071</PRODUCT_CODE>
        <DESCRIPTION>Tablet 20"</DESCRIPTION>
        <QUANTITY TYPE="MINQTY" UNIT="PCE">20.0000000000</QUANTITY>
        <QUANTITY TYPE="MAXQTY" UNIT="PCE">20.0000000000</QUANTITY>
        <QUANTITY TYPE="MINORDQTY" UNIT="PCE">0</QUANTITY>
        <PRICE TYPE="DIRECT">300.0000</PRICE>
        <PRICE TYPE="INDIRECT" />
        <PRICE TYPE="FINAL" />
        <CURRENCY>EUR</CURRENCY>
        <CATEGORY TYPE="1">0B</CATEGORY>
      </ITEM>
      <ITEM>
        <PRODUCT_CODE TYPE="BRAND">F11J115142AB</PRODUCT_CODE>
        <PRODUCT_CODE TYPE="EAN">8715555111333</PRODUCT_CODE>
        <DESCRIPTION>EMP-7773 VIDEOPROJECTOR</DESCRIPTION>
        <QUANTITY TYPE="MINQTY" UNIT="PCE">10.0000000000</QUANTITY>
        <QUANTITY TYPE="MAXQTY" UNIT="PCE">10.0000000000</QUANTITY>
        <QUANTITY TYPE="MINORDQTY" UNIT="PCE">0</QUANTITY>
        <PRICE TYPE="DIRECT">1500.0000</PRICE>
        <PRICE TYPE="INDIRECT" />
        <PRICE TYPE="FINAL" />
        <CURRENCY>EUR</CURRENCY>
        <CATEGORY TYPE="1">51</CATEGORY>
      </ITEM>
    </LINE_ITEMS>
  </QUOTATION>
</BRAND_QUOTES>

XML file:

<?xml version="1.0" encoding="UTF-8"?>
<BRAND_QUOTES xmlns:xalan="http://xml.apache.org/xalan" xmlns:java="http://xml.apache.org/xslt/java" xmlns:brand="http://brand" RECEIVER_ID="0000208712" SENDER_ID="1113214000020">
    <QUOTATION NUMBER="7001234567" DATE="20161025">
        <PARTY_DETAILS ROLE="SOLD_TO">
            <ID TYPE="GLN">0000208712</ID>
            <CONTACT_DETAILS>
                <NAME>CONCORD S.R.L.</NAME>
                <STREET>VIA GIGLI, 14</STREET>
                <COUNTRY>IT</COUNTRY>
                <POSTCODE>00118</POSTCODE>
                <PHONE>06/12345678</PHONE>
            </CONTACT_DETAILS>
        </PARTY_DETAILS>
        <PARTY_DETAILS ROLE="INDIRECT_CUSTOMER">
            <CONTACT_DETAILS>
                <NAME>FOX SRL</NAME>
                <VAT>IT01477465457</VAT>
            </CONTACT_DETAILS>
        </PARTY_DETAILS>
        <PARTY_DETAILS ROLE="SALES_EMPLOYEE">
            <CONTACT_DETAILS>
                <NAME>Mario Rossi</NAME>
            </CONTACT_DETAILS>
        </PARTY_DETAILS>
        <PARTY_DETAILS ROLE="CONTACT_PERSON">
            <CONTACT_DETAILS>
                <NAME>UFFICIO BID</NAME>
            </CONTACT_DETAILS>
        </PARTY_DETAILS>
        <PARTY_DETAILS ROLE="FINAL_CUSTOMER">
            <CONTACT_DETAILS>
                <NAME>WAST SPA</NAME>
            </CONTACT_DETAILS>
        </PARTY_DETAILS>
        <DATE TYPE="VALID_FROM">20161011</DATE>
        <DATE TYPE="VALID_TO">20170331</DATE>
        <LEGAL_TEXTS>
            <TEXT TYPE="FIXED_TEXT1">Faisant suite à notre dernier entretien,nous avons le plaisir de vous transmettre ci-dessous nos conditions pour cette affaire</TEXT>
            <TEXT TYPE="AUTO_TEXT">Le chiffre d'affaire réalisé sur cette cotation ne sera pas intégrédans  la base de calcul des bonus contractuels.</TEXT>
            <TEXT TYPE="FIXED_TEXT2">Cette offre est valable dans la limite des stocks disponibles et sous réserve de modification de prix de notre part, aux Conditions deréglements contractuelles, Nous vous prions d'identifier vos commandesavec la mention "Affaires Spéciales" en reportant le numéro decotation et le code marché et de joindre les justificatifs descommandes clients ,Nous souhaitons que ces conditions vous permettentde donner une suite favorable à cette affaire, Dans l'attente , nousvous prions de croire en l'assurance de notre considérationdistinguée.</TEXT>
            <TEXT TYPE="HEADER_TEXT"/>
        </LEGAL_TEXTS>
        <LINE_ITEMS>
            <ITEM>
                <PRODUCT_CODE TYPE="BRAND">V13H010L60</PRODUCT_CODE>
                <PRODUCT_CODE TYPE="EAN">8715946478609</PRODUCT_CODE>
                <DESCRIPTION>Lampe EB-93/95/96W/905/420/425W</DESCRIPTION>
                <QUANTITY TYPE="MINQTY" UNIT="PCE">90.0000000000</QUANTITY>
                <QUANTITY TYPE="MAXQTY" UNIT="PCE">90.0000000000</QUANTITY>
                <QUANTITY TYPE="MINORDQTY" UNIT="PCE">0</QUANTITY>
                <PRICE TYPE="DIRECT">120.5000</PRICE>
                <PRICE TYPE="INDIRECT">130.0000</PRICE>
                <PRICE TYPE="FINAL"/>
                <CURRENCY>EUR</CURRENCY>
            </ITEM>
            <ITEM>
                <PRODUCT_CODE TYPE="BRAND">V13H010L78</PRODUCT_CODE>
                <PRODUCT_CODE TYPE="EAN">8715946531045</PRODUCT_CODE>
                <DESCRIPTION>Lampe EB-SXW03/SXW18/X24</DESCRIPTION>
                <QUANTITY TYPE="MINQTY" UNIT="PCE">70.0000000000</QUANTITY>
                <QUANTITY TYPE="MAXQTY" UNIT="PCE">70.0000000000</QUANTITY>
                <QUANTITY TYPE="MINORDQTY" UNIT="PCE">0</QUANTITY>
                <PRICE TYPE="DIRECT">85.6600</PRICE>
                <PRICE TYPE="INDIRECT">92.0000</PRICE>
                <PRICE TYPE="FINAL"/>
                <CURRENCY>EUR</CURRENCY>
            </ITEM>
            <ITEM>
                <PRODUCT_CODE TYPE="BRAND">V13H010L88</PRODUCT_CODE>
                <PRODUCT_CODE TYPE="EAN">8715946546049</PRODUCT_CODE>
                <DESCRIPTION>Lampe EB-9xxH/SX27/W29</DESCRIPTION>
                <QUANTITY TYPE="MINQTY" UNIT="PCE">50.0000000000</QUANTITY>
                <QUANTITY TYPE="MAXQTY" UNIT="PCE">50.0000000000</QUANTITY>
                <QUANTITY TYPE="MINORDQTY" UNIT="PCE">0</QUANTITY>
                <PRICE TYPE="DIRECT">78.6000</PRICE>
                <PRICE TYPE="INDIRECT">82.0000</PRICE>
                <PRICE TYPE="FINAL"/>
                <CURRENCY>EUR</CURRENCY>
            </ITEM>
        </LINE_ITEMS>
    </QUOTATION>
</BRAND_QUOTES>

XSLT file:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/">
        <dataroot>
            <xsl:apply-templates select="@*|node()"/>
        </dataroot>
    </xsl:template>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="QUOTATION/@NUMBER">
        <xsl:apply-templates select="@*|node()"/>
    </xsl:template>

    <xsl:template match="CONTACT_DETAILS">
        <CONTACT_DETAILS>
            <QUOTATION><xsl:value-of select="../../QUOTATION/@NUMBER"/></QUOTATION>
            <xsl:apply-templates select="@*|node()"/>
        </CONTACT_DETAILS>
    </xsl:template>

    <xsl:template match="ITEM">
        <ITEM>
            <QUOTATION><xsl:value-of select="../../QUOTATION/@NUMBER"/></QUOTATION>
            <xsl:apply-templates select="@*|node()"/>
        </ITEM>
    </xsl:template>

    <xsl:template match="LEGAL_TEXTS">
        <LEGAL_TEXTS>
            <QUOTATION><xsl:value-of select="../../QUOTATION/@NUMBER"/></QUOTATION>
            <xsl:apply-templates select="@*|node()"/>
        </LEGAL_TEXTS>
    </xsl:template>

 <xsl:template match="PARTY_DETAILS">
        <PARTY_DETAILS>
            <QUOTATION><xsl:value-of select="../../QUOTATION/@NUMBER"/></QUOTATION>
            <xsl:apply-templates select="@*|node()"/>
        </PARTY_DETAILS>
    </xsl:template>

</xsl:stylesheet>

回答1:

Since you are using MS Access, most likely you require normalized database tables with a shared ID here being QUOTATION NUMBER and DATE. Consider the following XSLT, using ancestor::* to map the above two nodes to each descendant.

Additionally, since MS Access' XML import facility is element-centric and ignores any attributes, I converted attributes to elements in the XSLT templates. Finally, since XML maintains repeating child items TEXT, PRODUCT_CODE, QUANTITY, PRICE that align to databases' one-to-many table relationship, I incorporated a final template to map those in separate tables:

XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="QUOTATION">
    <xsl:copy>          
      <QUOTATION><xsl:value-of select="@NUMBER"/></QUOTATION>
      <DATE><xsl:value-of select="@DATE"/></DATE>
      <xsl:apply-templates select="*"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="PARTY_DETAILS|LEGAL_TEXTS">
    <xsl:copy>
      <QUOTATION>
        <xsl:value-of select="ancestor::BRAND_QUOTES/QUOTATION/@NUMBER"/>
      </QUOTATION>
      <DATE><xsl:value-of select="ancestor::BRAND_QUOTES/QUOTATION/@DATE"/></DATE>
      <xsl:if test="@*">
        <xsl:element name="{local-name(@*)}"><xsl:value-of select="@*"/></xsl:element>
      </xsl:if>
      <xsl:copy-of select="*[local-name()!='TEXT' and local-name()!='CONTACT_DETAILS'
                            and local-name()!='PRODUCT_CODE'
                            and local-name()!='QUANTITY' and local-name()!='PRICE']"/>
      <xsl:apply-templates select="CONTACT_DETAILS|TEXT|PRODUCT_CODE|QUANTITY|PRICE"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="CONTACT_DETAILS">
    <xsl:copy>
      <QUOTATION>
        <xsl:value-of select="ancestor::BRAND_QUOTES/QUOTATION/@NUMBER"/>
      </QUOTATION>
      <DATE><xsl:value-of select="ancestor::BRAND_QUOTES/QUOTATION/@DATE"/></DATE>      
      <xsl:copy-of select="*[local-name()!='VAT']"/>
      <VAT><xsl:value-of select="VAT"/></VAT>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="TEXT">
    <xsl:copy>
      <QUOTATION>
        <xsl:value-of select="ancestor::BRAND_QUOTES/QUOTATION/@NUMBER"/>
      </QUOTATION>
      <DATE><xsl:value-of select="ancestor::BRAND_QUOTES/QUOTATION/@DATE"/></DATE>
      <xsl:if test="@*">
        <xsl:element name="{local-name(@*)}"><xsl:value-of select="@*"/></xsl:element>
      </xsl:if>
      <VALUE><xsl:value-of select="substring(., 1, 255)"/></VALUE>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="ITEM">
    <xsl:copy>
      <QUOTATION>
        <xsl:value-of select="ancestor::BRAND_QUOTES/QUOTATION/@NUMBER"/>
      </QUOTATION>
      <DATE><xsl:value-of select="ancestor::BRAND_QUOTES/QUOTATION/@DATE"/></DATE>
      <xsl:for-each select="*">
        <xsl:if test="@*">
          <xsl:element name="{@*}"><xsl:value-of select="."/></xsl:element>
        </xsl:if>        
      </xsl:for-each>
      <xsl:copy-of select="*[not(@*)]"/>    
     </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

Access VBA (Using MSXML, more W3C compliant than built-in TransformXML)

Public Sub TransformXMLs()
    ' VBA REFERENCE: MSXML, v6.0
    Dim xmlDoc As New MSXML2.DOMDocument
    Dim xslDoc As New MSXML2.DOMDocument
    Dim newDoc As New MSXML2.DOMDocument

    xmlDoc.Load "C:\Path\To\Source.xml"
    xslDoc.Load "C:\Path\To\Stylesheet.xsl"

    xmlDoc.transformNodeToObject xslDoc, newDoc
    newDoc.Save "C:\Path\To\Output.xml"

    Application.ImportXML "C:\Path\To\Output.xml"

    Set xmlDoc = Nothing: Set xslDoc = Nothing: Set newDoc = Nothing
End Sub

Access Tables



标签： xml ms-access xslt import
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~