I have an XML in the following format:
<?xml version="1.0" encoding="utf-8"?>
<dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
<metadata>
<item name="Col1" type="xs:string" length="14"/>
<item name="Col2" type="xs:string" length="92"/>
<item name="Col3" type="xs:string" length="66"/>
<item name="Col4 With Space" type="xs:string" length="32"/>
</metadata>
<data>
<row>
<value>SomeVal1</value>
<value>SomeVal2</value>
<value>SomeVal3</value>
<value>SomeVal4</value>
</row>
<row>
<value>SomeVal11</value>
<value>SomeVal22</value>
<value>SomeVal33</value>
<value>SomeVal44</value>
</row>
</data>
</dataset>
I want to transform it to this format:
<?xml version="1.0" encoding="utf-8"?>
<dataset>
<data>
<row>
<Col1 type="xs:string" length="14">SomeVal1</Col1>
<Col2 type="xs:string" length="92">SomeVal2</Col2>
<Col3 type="xs:string" length="66">SomeVal3</Col3>
<Col4_With_Space type="xs:string" length="32">SomeVal4</Col4_With_Space>
</row>
<row>
<Col1 type="xs:string" length="14">SomeVal11</Col1>
<Col2 type="xs:string" length="92">SomeVal22</Col2>
<Col3 type="xs:string" length="66">SomeVal33</Col3>
<Col4_With_Space type="xs:string" length="32">SomeVal44</Col4_With_Space>
</row>
</data>
</dataset>
I've never used xslt before and I am out of my depth. I tried something like this (which doesn't work) but I'm stuck.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.w3.org/1999/xhtml" version="1.0">
<xsl:output encoding="UTF-8" indent="yes" method="xml" standalone="no" omit-xml-declaration="no"/>
<xsl:template match="dataset">
<xsl:for-each select="data/row">
<Col1 select="1"/>
<Col2 select="2"/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
The reason for wanting to transform is SSIS "Data Flow" "XML Source" doesn't recognise the first format. I'm trying to import data from an XML into a DB table. "XML Source" works with the 2nd format but not the first.
I think I should add, there is no guarantee XML metadata will always be the same, so transform needs to take the column names and types from the metadata, instead of hard-coding.