vba – Selecting column names correctly when converting XML (schema included in file) using a XSL file

I have the following XML file I need to convert.

Original 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>

I am using the following XSL file to convert this file.

XSL converter

<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>

After conversion, I import the new XML into MS Access, creating a table. The result is this: Table results image

The name() function in line <xsl:element name="{name()}"> is returning the value of the name attribute on the XML schema(C0, C1, C2, etc). The correct column names are in the rs:name attribute in the schema, but after hours of work I was not able to figure out how to correctly grab the column titles.

Can anyone point me to the right direction here? How do I get the column names from the XML schema?

Thanks in advance!

Leave a Comment