使用XSL檔案轉換XML（檔案中包含的模式）時正確選擇列名-有解無憂

我有以下需要轉換的 XML 檔案。

原始 XML

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly'>
        <s:AttributeType name='c0' rs:name='v.id_viag' rs:number='1' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='19' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c1' rs:name='v.dt_cria' rs:number='2' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='6' rs:precision='26' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c2' rs:name='v.id_usu_cria' rs:number='3' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c3' rs:name='v.dt_ult_atualz' rs:number='4' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='6' rs:precision='26' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c4' rs:name='v.id_usu_ult_atualz' rs:number='5' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c5' rs:name='v.id_viag_sit' rs:number='6' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='19' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c6' rs:name='v.id_viag_objtv' rs:number='7' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='19' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c7' rs:name='v.id_viajt' rs:number='8' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='19' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c8' rs:name='v.nu_viag' rs:number='9' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='9' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c9' rs:name='v.dt_ptcol' rs:number='10' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='6' rs:precision='26' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c10' rs:name='v.dt_cria_viag' rs:number='11' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='6' rs:precision='26' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c11' rs:name='v.dt_ini_viag' rs:number='12' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='6' rs:precision='26' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c12' rs:name='v.dt_fim_viag' rs:number='13' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='6' rs:precision='26' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c13' rs:name='v.dt_canclmt' rs:number='14' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='6' rs:precision='26' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c14' rs:name='v.ds_dtlhmt_objtv_viag' rs:number='15' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c15' rs:name='v.ds_justfcv_ptp' rs:number='16' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c16' rs:name='v.in_rduc_aux_viag' rs:number='17' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c17' rs:name='v.ds_justfcv_oa' rs:number='18' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c18' rs:name='v.vl_oa' rs:number='19' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='2' rs:precision='15' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c19' rs:name='v.ds_obs_viag' rs:number='20' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c20' rs:name='v.vl_aux_viag' rs:number='21' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='2' rs:precision='15' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c21' rs:name='v.sg_cod_centro_cust' rs:number='22' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c22' rs:name='v.in_envd_dou' rs:number='23' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c23' rs:name='v.in_aprv_viag' rs:number='24' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c24' rs:name='v.dt_ult_export_ptp' rs:number='25' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='6' rs:precision='26' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c25' rs:name='v.in_pend_export_ptp' rs:number='26' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c26' rs:name='v.sg_cod_centro_cust_aprvdr' rs:number='27' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:AttributeType name='c27' rs:name='v.id_locc_cmptlhd' rs:number='28' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='19' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c28' rs:name='v.nm_org_viajt' rs:number='29' rs:nullable='true' rs:write='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='255'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
    </s:ElementType>
</s:Schema>
<rs:data>
    <z:row c0='46162' c1='2021-12-31T08:14:52.055421000' c2='UC_VIA' c3='2022-01-11T17:18:20.136658000' c4='UC_VIA'
         c5='8' c6='31' c7='15528' c8='202220019' c9='2021-12-31T08:14:51.645000000' c10='2021-12-31T08:14:51.645000000'
         c11='2022-01-06T00:00:00' c12='2022-01-07T00:00:00' c14='Reuni?es com SPU.' c16='N' c19='OUTROS' c21='DIR5'
         c22='N' c23='S' c25='N' c26='DIR5' c28='GP'/>
    <z:row c0='46183' c1='2021-12-30T08:14:49.253637000' c2='UC_VIA' c3='2022-01-25T21:06:39.985815000' c4='UC_VIA'
         c5='8' c6='31' c7='2435' c8='202220017' c9='2021-12-30T00:00:00' c10='2021-12-30T08:14:49.129000000' c11='2022-01-20T00:00:00'
         c12='2022-01-21T00:00:00' c14='Realiza??o da Audiência Pública da PPP Prisional de Blumenau/SC' c16='N' c21='AGOV/DEPS1'
         c22='N' c23='S' c25='S' c26='AGOV/DEPS1' c28='AGOV/DEPS1'/>
</rs:data>
</xml>

我正在使用以下 XSL 檔案來轉換此檔案。

XSL 轉換器

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:rs="urn:schemas-microsoft-com:rowset"
 exclude-result-prefixes="rs">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
 <!-- root element for the XML output -->
<rootElement xmlns:z="#RowsetSchema" xsl:exclude-result-prefixes="z">

    <!-- for each z:row element in the ADO output -->
    <xsl:for-each select="/xml/rs:data/z:row">

        <!--
            This will be used for the table name imported into Access.
            Change this name to suit your needs.
        -->
        <TableName>
            <!-- 
                for each attribute of the z:row element in the ADO XML document
            -->
            <xsl:for-each select="@*">
                                
                <!-- 
                dynamically create elements and fill with attribute 
                value using the XPath name() function
                -->
                <xsl:element name="{name()}">
                    <xsl:value-of select="."/>
                </xsl:element>

            </xsl:for-each>
        </TableName>

    </xsl:for-each>
</rootElement>
</xsl:template>
</xsl:stylesheet>

轉換后，我將新的 XML 匯入 MS Access，創建一個表。結果是這樣的：表格結果圖片

行中的name()函式<xsl:element name="{name()}">回傳nameXML 模式（C0、C1、C2 等）上的屬性值。正確的列名位于rs:name模式中的屬性中，但經過數小時的作業，我無法弄清楚如何正確獲取列標題。

誰能在這里指出我正確的方向？如何從 XML 模式中獲取列名？

提前致謝！

uj5u.com熱心網友回復：

XSLT 具有用于解決交叉參考的內置密鑰機制。我強烈建議您利用它：

XSLT 2.0

<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'
exclude-result-prefixes="#all">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:key name="col" match="s:AttributeType" use="@name" />

<xsl:template match="/xml">
    <rootElement>
        <xsl:for-each select="rs:data/z:row">
            <TableName>
                <xsl:for-each select="@*">
                    <xsl:element name="{key('col', name())/@rs:name}">
                        <xsl:value-of select="."/>
                    </xsl:element>
                 </xsl:for-each>
            </TableName>
         </xsl:for-each>
    </rootElement>
</xsl:template>

</xsl:stylesheet>

uj5u.com熱心網友回復：

考慮搜索與當前屬性s:AttributeType匹配的節點，然后回傳對應的。也用于將點（資料庫列名中的特殊字符）轉換為下劃線。注意為in root 添加的前綴宣告，因為 XPath 使用它。@namename()rs:nametranslate()s

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                              xmlns:rs="urn:schemas-microsoft-com:rowset"
                              xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
                exclude-result-prefixes="rs">
  <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
  
  <xsl:template match="/">
    <!-- root element for the XML output -->
    <rootElement xmlns:z="#RowsetSchema" xsl:exclude-result-prefixes="z">
      <!-- for each z:row element in the ADO output -->
      <xsl:for-each select="/xml/rs:data/z:row">
        <!--
            This will be used for the table name imported into Access.
            Change this name to suit your needs.
        -->
        <TableName>
            <!-- 
                for each attribute of the z:row element in the ADO XML document
            -->
            <xsl:for-each select="@*">
                <!-- CAPTURE CURRENT ATTRIBUTE NAME    -->
                <xsl:variable name="curr_name" select="name()"/>
                <!-- SEARCH s:AttributeType FOR MATCHING @name, RETURN @rs:name -->
                <xsl:variable name="rs_name" select="//s:AttributeType[@name=$curr_name]/@rs:name"/>
                
                <xsl:element name="{translate($rs_name, '.', '_')}">
                    <xsl:value-of select="."/>
                </xsl:element>
            </xsl:for-each>
        </TableName>
    </xsl:for-each>    
    </rootElement>
  </xsl:template>  
</xsl:stylesheet>

Online Demo

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/484402.html

標籤：xml vba 毫秒访问 xslt

上一篇：如何使用fop將Tesseract軟體輸出（hocr）轉換為純txt檔案（生成零輸出）？

下一篇：通知渠道應用程式崩潰并停止