1

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,

enter image description here

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

Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
Reshma
  • 864
  • 5
  • 20
  • 38
  • Tried using XML source? – Raj Sep 11 '13 at 10:15
  • @Raj...I have executed my entire issue by just web service task and Execute SQL task in control flow, I don't want to execute any data flow component, by Execute SQL task component itself I am able to insert data into database but just in appropriate format, so If I make use of XML Source then I will have to work in Data flow ..isn't it...?..which I don't want to do actually, isn't there any other way that can work in XML Task itself.. – Reshma Sep 11 '13 at 10:21
  • In that case, why not use XQuery in EXECUTE SQL task and shred the XML? – Raj Sep 11 '13 at 10:27
  • @Raj.. sorry, I am new to SSIS so unable to understand properly what you explained, if to use xquery in Execute SQL Task , I am not able to find any such option in Execute SQL Task, do u mean to say XML Task and also to shred the XML, I found some helping links for it for eg [http://www.allaboutmssql.com/2013/08/ssis-shred-data-from-xml-file-in-folder.html] which again shows to work with data flow which I don't want to use. – Reshma Sep 11 '13 at 10:37
  • What is your reason for not working with data flow? – shree.pat18 Sep 13 '13 at 02:33
  • @shree.pat18...I am just trying to insert result from web service task into data base whereas I have written my query in Execute SQL Task to insert data from variable in control flow itself, when I am moving to data flow to access my variable holding result from web service task then I am unable to access the variable in any source or destination..and also I don't want to use any source, I just need Destination – Reshma Sep 16 '13 at 05:15

3 Answers3

0

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

Raj
  • 10,653
  • 2
  • 45
  • 52
  • @Raj.. so r u suggesting me that in my Execute sql task where I have written my insert query to insert data into database has to be manipulated by something like you have given above.?..can you please check out this link [http://stackoverflow.com/questions/18691959/inserting-data-into-sql-server-from-variables-via-ssis] and suggest me is it possible to do whatever I exactly want? – Reshma Sep 11 '13 at 11:05
0

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.

Community
  • 1
  • 1
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
0

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

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

Esteban Perez
  • 1