Conversion of Data through XML task vis SSIS

Question

I have consumed simple web service for addition of numbers and returns the result in variable which is in the following format

<?xml version="1.0" encoding="utf-16"?>
<int>35</int>

So when I try to insert this 35 into database through Execute SQL Task then whole of the xml content given above is inserted into database, so I used XML Tsk in between web service task and Execute SQL Task, it's screen shot is as follows,

Still it is not able to get the node value that is 35 to insert it into database.

Answer 1

You can use an EXECUTE SQL TASK and use code similar to this in the task:

DECLARE @xml xml
SET @xml = '<?xml version="1.0"?>
<int>35</int>'

SELECT @xml.value('int[1]','int')

Of course, you will need to modify that query to insert and modify the @xml variable to your source variable. I am only trying to point you in the right direction.

Raj

Answer 2

Reshma you have four questions open about this same topic and it's very confusing. Please don't ask any more questions until you have resolved your issue fully. In your case you can use exactly the same method I described in

Inserting Data into SQL Server from variables via SSIS

To generate the string that Raj suggested, then run that in an execute SQL task and capture the result.

The alternative trivial solution is to use string expressions to extract the number from the XML. but that is not 'proper' XML shredding.

Can you clarify, is there a business need behind this or are you just learning about XML? SSIS is not the best tool for shredding and processing XML.

Answer 3

You must change the 'SecondOperand' value to //int

If you want to save the result, configure 'OperationResult' properties.