Importing a XML file in MS Access

2019-07-31 10:08发布

站内问答 / 移动开发
1779 1 6

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条回答
男人必须洒脱
2楼-- · 2019-07-31 10:25

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

DB XML Import

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)