transform tag attributes into xml tags

guitarzan8 · April 21st, 2010, 11:30 AM

processor: SQL Server 2005 > SSIS package > XML Task.
platform: Win 7

source XML:

Code:

<FCSDServicesResponse country="USA" language="ENG" marketid="1">
<Status desc="Web service status OK" id="VI2000"/>
<Vehicles>
<Vehicle vin="2LMDU88CX8BJ29781">
<Status desc="Vehicle status OK" id="VI1000"/>
<VehicleAttributes>
<VehicleAttribute type="VehicleType" typeid="12" value="Truck" valueid="4673"/>
<VehicleAttribute type="Year" typeid="14" value="2008" valueid="4285"/>
<VehicleAttribute type="BodyStyle" typeid="15" value="Sport Utility Vehicle" valueid="4698"/>
<VehicleAttribute type="Make" typeid="1" value="Lincoln" valueid="185"/>
<VehicleAttribute type="Model" typeid="2" value="MKX" valueid="4240"/>
<VehicleAttribute type="Series" typeid="3" value="" valueid="105"/>
</VehicleAttributes>
</Vehicle>
<Vehicle vin="5LMFL28517LJ13311">
<Status desc="Vehicle status OK" id="VI1000"/>
<VehicleAttributes>
<VehicleAttribute type="VehicleType" typeid="12" value="Truck" valueid="4673"/>
<VehicleAttribute type="Year" typeid="14" value="2007" valueid="4093"/>
<VehicleAttribute type="BodyStyle" typeid="15" value="Sport Utility Vehicle" valueid="4698"/>
<VehicleAttribute type="Make" typeid="1" value="Lincoln" valueid="185"/>
<VehicleAttribute type="Model" typeid="2" value="Navigator L" valueid="4343"/>
<VehicleAttribute type="Series" typeid="3" value="" valueid="105"/>
</VehicleAttributes>
</Vehicle>
</Vehicles>
</FCSDServicesResponse>

the XSLT I currently have.

Code:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/FCSDServicesResponse">
    <Vehicles>
		<xsl:variable name="VIN" select="Vehicles/Vehicle/@vin" />
		<xsl:for-each select="Vehicles/Vehicle">
        <Vehicle>
            <VIN><xsl:value-of select="$VIN"/></VIN>
            <Status><xsl:value-of select="Status/@desc"/></Status>
            <VehicleType><xsl:value-of select="VehicleAttributes/VehicleAttribute/@VehicleType"/></VehicleType>
            <Year><xsl:value-of select="VehicleAttributes/VehicleAttribute/@Year"/></Year>
            <BodyStyle><xsl:value-of select="VehicleAttributes/VehicleAttribute/@BodyStyle"/></BodyStyle>
            <Make><xsl:value-of select="VehicleAttributes/VehicleAttribute/@Make"/></Make>
            <Model><xsl:value-of select="VehicleAttributes/VehicleAttribute/@Model"/></Model>
            <Series><xsl:value-of select="VehicleAttributes/VehicleAttribute/@Series"/></Series>
        </Vehicle>
    </xsl:for-each>
    </Vehicles>
</xsl:template>
</xsl:stylesheet>

the desired output.

Code:

<?xml version="1.0" encoding="utf-8"?>
<Vehicles>
  <Vehicle>
    <VIN>2LMDU88CX8BJ29781</VIN>
    <Status>Vehicle status OK</Status>
    <VehicleType>Truck</VehicleType>
    <Year>2008</Year>
    <BodyStyle>Sport Utility Vehicle</BodyStyle>
    <Make>Lincoln</Make>
    <Model>MKX</Model>
    <Series></Series>
  </Vehicle>
  <Vehicle>
    <VIN>5LMFL28517LJ13311</VIN>
    <Status>Vehicle status OK</Status>
    <VehicleType>Truck</VehicleType>
    <Year>2007</Year>
    <BodyStyle>Sport Utility Vehicle</BodyStyle>
    <Make>Lincoln</Make>
    <Model>Navigator L</Model>
    <Series></Series>
  </Vehicle>
</Vehicles>

the actual output.

Code:

<?xml version="1.0" encoding="utf-8"?>
<Vehicles>
  <Vehicle>
    <VIN>2LMDU88CX8BJ29781</VIN>
    <Status>Vehicle status OK</Status>
    <VehicleType></VehicleType>
    <Year></Year>
    <BodyStyle></BodyStyle>
    <Make></Make>
    <Model></Model>
    <Series></Series>
  </Vehicle>
  <Vehicle>
    <VIN>5LMFL28517LJ13311</VIN>
    <Status>Vehicle status OK</Status>
    <VehicleType></VehicleType>
    <Year></Year>
    <BodyStyle></BodyStyle>
    <Make></Make>
    <Model></Model>
    <Series></Series>
  </Vehicle>
</Vehicles>

My output expectation is that I want VehicleType, Year, BodyStyle, Make, Model and Series to contain data. How do I form the "xsl:value-of" to get what I want?

guitarzan8 · April 21st, 2010, 02:56 PM

A co-worker exposed me to XPATH which I've never used before. In my platform I can throw XPATH commands at the XML. Is there an XPATH command that would extract the attributes from the VehicleAttribute element?
I applied a

Code:

//Vehicles

command and the results allowed me to remove a bunch of vbscript data scrubbing to replace the 1st two lines. Is it possible to convert the VehicleAttribute attributes to nodes? Something like:

Code:

/Vehicles/Vehicle/@Make

?