XML task error
I am trying to get my SSIS XML Task to take a XML document and use XSLT to extract certain attributes or elements.
The configuration is as follows:
OperationType XSLT
SourceType Variable
Source User::Results
SaveOperationResult True
OperationResult
DestinationType Variable
Destination User::VarResults
OverwriteDestination True
SecondOperandType File Connection
SecondOperand Type.xsl
SSIS ERROR:
[XML Task] Error: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
[XML Task] Error: "Root element is missing.".
SQL Query:
SELECT Type, TypeID5, TypeID4, TypeID3, TypeID2, TypeID1 FROM dbo.Temp_Type as TT
for xml auto, elements, type, root('Types')
Types.XSL file:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="Types">
<html>
<head>
<style type="text/css">
body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}
h1 {color: #f00;}
h2 {size: 50%; color: #00f;}
th {padding-right: 10px; font: 'Trebuchet MS'; font-size: 9pt; font-weight:bold;}
td {padding-right: 10px; font: 'Trebuchet MS'; font-size: 9pt;}
</style>
</head>
<body>
<h2>Text</h2>
<DIV style="margin-bottom:20px;">
<table>
<tr>
<th>Type</th>
<th>TypeID5</th>
<th>TypeID4</th>
<th>TypeID3</th>
<th>TypeID2</th>
<th>TypeID1</th>
</tr>
<xsl:apply-templates select="TT"/>
</table>
<hr style="color:#ff6600; height:1px;"/>
</DIV>
</body>
</html>
</xsl:template>
<xsl:template match="TT">
<tr>
<td><xsl:value-of select="Type"/></td>
<td><xsl:value-of select="TypeID5"/></td>
<td><xsl:value-of select="TypeID4"/></td>
<td><xsl:value-of select="TypeID3"/></td>
<td><xsl:value-of select="TypeID2"/></td>
<td><xsl:value-of select="TypeID1"/></td>
</tr>
</xsl:template>
</xsl:stylesheet>
March 21st, 2011 9:48pm
How does your XML look like?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
March 22nd, 2011 11:38am
Hi...Please find xml below.
<Types>
<TT>
<Type>1</Type>
<TypeID5>12345</TypeID5>
<TypeID4>34555</TypeID4>
<TypeID3>34901</TypeID3>
<TypeID2>8904</TypeID2>
<TypeID1>9903</TypeID1>
</TT>
<TT>
<Type>2</Type>
<TypeID5>972</TypeID5>
<TypeID4>854</TypeID4>
<TypeID3>2290</TypeID3>
<TypeID2>3497</TypeID2>
<TypeID1>5219</TypeID1>
</TT>
</Types>
March 22nd, 2011 3:18pm
Based on what I see you better off to loading the whole XML file into a table. Then you can read whatever values you need from that table.
To load XML to a table is a matter of setting up an XML Source and an OLEDB target.
Here is an example:
http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx
Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
March 22nd, 2011 10:35pm