Retrieving node values from XML file and storing the data into database via SSIS

Question

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,

So according to this question: stackoverflow.com/questions/18738310/… you have can insert the entire XML file into a database column. Is that correct? — Nick.Mc
– Nick.Mc, Commented Sep 19, 2013 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
– Reshma, Commented Sep 19, 2013 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: sqlservercentral.com/Forums/Topic1424325-3331-1.aspx and this: stackoverflow.com/questions/6309410/…. This one uses SSIS to shred it but doesn't explain how it does it: sqlservercentral.com/articles/Integration+Services+(SSIS)/96579 — Nick.Mc
– Nick.Mc, Commented Sep 22, 2013 at 1: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
– Nick.Mc, Commented Sep 22, 2013 at 1: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
– Reshma, Commented Sep 23, 2013 at 8:04

Ashish · Accepted Answer · 2013-09-16 08:51:50Z

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

answered Sep 16, 2013 at 8:51

Ashish

33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Retrieving node values from XML file and storing the data into database via SSIS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related