x

[Closed] SSIS - Need help with XML task for XSLT operation

I need help with with XML task for XSLT operation in SSIS. I have a table as below that I am generating a XML output. I give that to a XML task inside SSIS with the XSLT operation, such that the output is a well formatted HTML script. The SSIS is executing successfully, however I am not getting output as desired. The table headers appear but the table data is not present. What am I missing?

Initial architecture-

 use tempdb
 go
 create table employeelist(
 empid smallint identity (1,1),
 name varchar(10),
 dept varchar(10)
 )
 go
 insert into employeelist (name,dept) values ('Tim','Sales')
 insert into employeelist (name,dept) values ('Tina','Sales')
 insert into employeelist (name,dept) values ('Steve','Production')
 insert into employeelist (name,dept) values ('Ashley','Marketing')
 insert into employeelist (name,dept) values ('Viola','Marketing')
 go

Query inside the Execute SQL Task, with the resultset as XML

 select
 name,
 dept
 from employeelist
 FOR XML PATH('Row')

The XSL program that I give as second Operand

 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="html" indent="yes"/>
     <xsl:template match="/">
         <table border="1" cellpadding="1">
             <tr>
                 <td>EmployeeName</td>
                 <td>DepartmentName</td>
             </tr>
             <xsl:for-each select="Row">
                 <tr>
                     <xsl:value-of select="." />
                 </tr>
             </xsl:for-each>
         </table>
     </xsl:template>
 </xsl:stylesheet>

Output I am getting

Image for Output

Any suggesstions?

html_op.jpg (20.1 kB)
more ▼

asked Dec 03, 2014 at 01:11 PM in Default

avatar image

nidheesh.r.pillai
9.5k 23 59 49

(comments are locked)
10|1200 characters needed characters left

The question has been closed Mar 27, 2015 at 09:34 AM by nidheesh.r.pillai for the following reason:

The question is answered, right answer was accepted


1 answer: sort voted first

The problem is with the XSL code. Please try this, it worked for me!!!

 <?xml version="1.0" encoding="utf-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
     xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
 >
     <xsl:output method="html" indent="yes"/>
         <xsl:template match="/ROOT">
             <table border="1" cellpadding="1">
                 <tr bgcolor="#FFFF">
                     <th>EmployeeName</th>
                     <th>DepartmentName</th>
                 </tr>
                 <xsl:for-each select="row">
                     <tr>
                         <td>
                             <xsl:value-of select="name" />
                         </td>
                         <td>
                             <xsl:value-of select="dept" />
                         </td>
                     </tr>
                 </xsl:for-each>
             </table>
         </xsl:template>
     </xsl:stylesheet>
 
more ▼

answered Dec 05, 2014 at 09:11 AM

avatar image

LadyProgrammer
91 2 6

Wonderful! Thanks a lot!

Dec 05, 2014 at 10:58 AM nidheesh.r.pillai
(comments are locked)
10|1200 characters needed characters left

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x1221
x203
x4

asked: Dec 03, 2014 at 01:11 PM

Seen: 883 times

Last Updated: Dec 05, 2014 at 10:58 AM

Copyright 2017 Redgate Software. Privacy Policy