0

I have used a web service task to give the following result in XML File as follows,

<?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://Iycon.com/WebService">Reshma</Name>
  <BirthDate xmlns="http://Iycon.com/WebService">1988-09-23T00:00:00</BirthDate>
</Data>

, now I am intended to insert two values in database, initially I worked with single value and I inserted that value into database from web service task using execute SQL task, but with this multiple values ,I am facing problem, Unable to use XML task to retrieve the values from two node, after some study got to know about Foreach loop container but failing to use it properly so that I can traverse through both of them node and insert that data into database, Any help will be greatly appreciated,

Reshma
  • 864
  • 5
  • 20
  • 38
  • So according to this question: http://stackoverflow.com/questions/18738310/conversion-of-data-through-xml-task-vis-ssis you have can insert the entire XML file into a database column. Is that correct? – Nick.Mc Sep 19 '13 at 11:07
  • @ElectricLlama ..Ya,I can store the entire XML in database column but my main requirement I want to store only the node value from the XML file into the database. – Reshma Sep 19 '13 at 12:22
  • Any reason that you want to extract the node in SSIS and not the database? I cannot find a properly explained example shredding XML inside SSIS - they all load it into the database then shred it in there. Take a look at these two examples of shredding an XML column into individual values: http://www.sqlservercentral.com/Forums/Topic1424325-3331-1.aspx and this: http://stackoverflow.com/questions/6309410/shredding-xml-from-db-using-ssis. This one uses SSIS to shred it but doesn't explain how it does it: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/96579/ – Nick.Mc Sep 22 '13 at 01:37
  • The approach I'm getting at: 1. Store the full XML document in the database (you have already managed to do this). 2. Shred the data out of the XML file into further columns using SQL XML functionality. Regardless you need to know the XQuery syntax to extract the nodes correctly. – Nick.Mc Sep 22 '13 at 01:41
  • @ElectricLlama... No matter what I am doing, I also tried by above method but the fact is I can store the whole of xml file in database but I am not able to read any of the value in foreach loop container, when I am trying to read the values of variable via breakpoints then the variable is showing empty value...unable to understand the problem – Reshma Sep 23 '13 at 08:04
  • Forget about the for loop. Look carefully at this example: http://www.sqlservercentral.com/Forums/Topic1424325-3331-1.aspx They have ONE record (XML file) in a table. Then they have used `.nodes` to turn that one record into _7_ columns. You only need to get two columns. That is the way I suggest you do it. Do you know the correct XPath to shred out the data you need? – Nick.Mc Sep 23 '13 at 10:07
  • @ElectricLlama.. I am sorry but I don't know the correct XPath to shred out the data. – Reshma Sep 23 '13 at 10:15
  • Don't be sorry, I suggest you just go and look those things up online and understand what they mean. This is a good start: http://www.w3schools.com/xpath/xpath_intro.asp. In the meantime I'll see if I can find something else to help. – Nick.Mc Sep 23 '13 at 11:11
  • Here is a practical example of XPath: go to this site: http://videlibri.sourceforge.net/cgi-bin/xidelcgi. Paste your XML into the left pane. paste `//Name` into the right pane. The output appears below - the contents of the name node. Now you need to take a look at the example code in this link sqlservercentral.com/Forums/Topic1424325-3331-1.aspx they are using XPath to shred the nodes out of the XML. – Nick.Mc Sep 23 '13 at 11:28

1 Answers1

0

there is a good article on foreach loop container hv a look at it

https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-the-foreach-loop-container/

when you say two value is it text between tag separated by some delimiter or child node in it

any example will help

Ashish Shevale

Ashish
  • 3
  • 3