To read Xml file from variable in XML Source in SSIS

Question

I am getting the result in variable through web Service Task as shown in below image,

Then I have used XML task to remove the excess namespace and stored the processed result in another variable in strProcessedResult Variable as shown in below image,

here Second operand is my data.xslt file which helps to remove extra namespace from my XML file,

And below is the whole structure of my control flow,

In Data flow , I am using XML source to read my Variable and to enter the Data into database, below is the screenshot of XML Source,

,

Here, it's giving me error as XML source was unable to read the data and XMLDataVariable was empty, I want to read the node value and insert the data into database using OLEDB Destination,

In Case if instead of saving the result from web service task into variable, I save that in XML file externally then below will be my XML File format,

<?xml version="1.0" encoding="utf-16"?>
 <Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Name xmlns="http://localhost">Rahul</Name>
  <BirthDate xmlns="http://localhost">1991-04-20T00:00:00</BirthDate>
 </Data>.

And after applying XSLT rule to remove excess namespace, my XML file will look like this,

<?xml version="1.0" encoding="utf-8"?><Data><Name>Rahul</Name><BirthDate>1991-04-20T00:00:00</BirthDate></Data>

And please make note, I have to compulsorily add the XSLT rule to remove the extra namespace otherwise it does not create the XSD for further operation and also my project works well If I store result in XML file externally but I want to make use of variable for whole operation,

Answer 1

Make sure you actually have something in your strProcessedResult variable at design time. By that I mean: when you run your package it will load the variable but at design time your variable will be empty, so paste in your expected XML, just to give the task something to process.