Home > Back-end >  Selecting column names correctly when converting XML (schema included in file) using a XSL file
Selecting column names correctly when converting XML (schema included in file) using a XSL file

Time：05-28

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!

CodePudding user response：

Consider searching the s:AttributeType nodes for matching @name to current attribute name(), then return corresponding rs:name. Also use translate() to convert the dots (special character in database column names) to underscore. Note the added prefix declaration for s in root since XPath uses it.

<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

CodePudding user response：

XSLT has a built-in key mechanism for resolving cross-references. I strongly recommend you take advantage of it:

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>

Page link：https//www.codepudding.com/Backend/422571.html

Prev:RSI in spyder using data in excel
Next:Deserialising XML with name and type as attributes
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding