SSIS: How to shred XML variable and save parts as other variables

Hi All

I have a SSIS 2008 R2 package with a Script Task in C# {code supplied by someone else, butchered by me} that performs a Web Service call and stores the result in XML with three values in a string variable that looks like the below XML.  Good to go.
<ArrayOf CustomerAccount xmlns:i="http://w3.org/blah blah blah" xmlns="http://blah blah blah">
<Customer Account>
   <ContactEmail> whacka@doo.com</ContactEmail> 
   <Name>Michael Stipe</Name>
   <Number>42</Number>
</CustomerAccount>
</ArrayOf CustomerAccount>

Open in new window


Question:  Is there a way in script (or in anything else) to take this XML string variable, grab the three values, and store them as other variables?

Due to customer requirements I am not allowed to write this to a file or save these directly to a table without encrypt them first (I can handle the encryption part), as all three of these values are considered confidential.

Thanks in advance.
Jim
LVL 67
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher KileCommented:
What you want to do is easily accomplished in VB.Net or C# by any of four methods.  Three of them are described here, and the fourth (reading into a DataSet) is described here and here.

Did you want to pass these values from one task to another, or use them locally within a specific task?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
... looking ...
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Both links described here (VB.NET) and here (C#)  starts with reading from an .xml file, and I have my xml already in a string variable strResult.

Below current SSIS C# code, slightly mocked up, which I probably should have posted with the original question.  

I have confirmed that the call works correctly and strResult contains the return XML that matches the one in the original question.
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;

using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

using System.Net;
using System.IO;


namespace ST_32c35746b01c475385d5c827c55e0818.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
 
        public void Main()
        {

            string sCode1 = Dts.Variables["Code1"].Value.ToString();
            string sCode2 = Dts.Variables["Code2"].Value.ToString();

            string webservice = "";

            //hard coded way to verify it works.
            var url = "http://goo.foo.boo/payment/Method?Code1=" + sCode1 + "&Code2=" + sCode2;

            MessageBox.Show(url);

            var _httpWReq = (HttpWebRequest)WebRequest.Create(url);
            _httpWReq.Credentials = CredentialCache.DefaultNetworkCredentials;
            _httpWReq.Method = "GET";
            _httpWReq.ContentType = "text/xml";


            var x = (HttpWebResponse)_httpWReq.GetResponse(); //the exception :(
            var strResult = new StreamReader(x.GetResponseStream()).ReadToEnd();

            MessageBox.Show(strResult);

            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Christopher KileCommented:
*sigh* The reason I gave you those methods is that, depending on how you plan to access the data, one way would be easier that another.  For small XML files, especially those with nested data (nodes within nodes), reading the XML file directly into an ADO.Net DataSet is infinitely preferable.  The following program assumes you start with the XML string strResult (which I've supplied with some edits to allow it to be parsed - for some reason it didn't like your namespace definition ;-) ):

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;

namespace XMLDataSetGetSelectedValues
{
    class Program
    {
        static void Main(string[] args)
        {
            string strResult = "<ArrayOfCustomerAccount>"
                            + "<CustomerAccount>"
                            + "<ContactEmail> whacka@doo.com</ContactEmail> "
                            + "<Name>Michael Stipe</Name>"
                            + "<Number>42</Number>"
                            + "</CustomerAccount>"
                            + "</ArrayOfCustomerAccount> ";

            // XmlTextReader resultXMLReader = new XmlTextReader(x.GetResponseStream());

            
            XmlTextReader resultXMLReader = new XmlTextReader(new StringReader(strResult));
            DataSet dsResult = new DataSet();

            dsResult.ReadXml(resultXMLReader);
            dsResult.AcceptChanges();
            resultXMLReader.Close();
            Console.WriteLine("Contact Email: " + dsResult.Tables[0].Rows[0]["ContactEmail"]);
            Console.WriteLine("Name         : " + dsResult.Tables[0].Rows[0]["Name"]);
            Console.WriteLine("Number       : " + dsResult.Tables[0].Rows[0]["Number"]);
            Console.ReadLine();
        }
    }
}

Open in new window


This code reads the XML into a DataSet and displays the fields of the only record it read.  You should be able to easily insert the relevant code into your own program using this as a source.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
... looking ...

Yeah one of the requirements is that this confidential info can only be access via a web service, executed once for each record needed, and stored in tables only encrypted.   I'd have much preferred a single T-SQL call grabbing the entire set, but it's their nickel.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Bingo bango.

The Web guys are still working on getting the method right, but based on the agreed-upon schema, hard-coded in the below script, we're good.

Thanks.

 
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

using System.Net;
using System.IO;
using System.Xml;

namespace ST_32c35746b01c475385d5c827c55e0818.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            string sCustomerCode = Dts.Variables["sCustomerCode"].Value.ToString();
            string sSourceSystemCode = Dts.Variables["sSourceSystemCode"].Value.ToString();

            string webservice = "";

            //need to build through variables

            //hard coded way to verify it works.
            var url = "http://goo.foo.boo/payment/method?SourceSystemCode=027&CustomerCode=101120";

            MessageBox.Show(url);

            var _httpWReq = (HttpWebRequest)WebRequest.Create(url);
            _httpWReq.Credentials = CredentialCache.DefaultNetworkCredentials;
            _httpWReq.Method = "GET";
            _httpWReq.ContentType = "text/xml";


            var x = (HttpWebResponse)_httpWReq.GetResponse(); //the exception :(
            var strResult = new StreamReader(x.GetResponseStream()).ReadToEnd();

            MessageBox.Show(strResult);

            strResult = "<ArrayOfCustomerAccount>"
                            + "<CustomerAccount>"
                            + "<ContactEmail>whacka@doo.com</ContactEmail> "
                            + "<Name>Michael Stipe</Name>"
                            + "<Number>42</Number>"
                            + "</CustomerAccount>"
                            + "</ArrayOfCustomerAccount> ";

            // XmlTextReader resultXMLReader = new XmlTextReader(x.GetResponseStream());


            XmlTextReader resultXMLReader = new XmlTextReader(new StringReader(strResult));
            DataSet dsResult = new DataSet();

            dsResult.ReadXml(resultXMLReader);
            dsResult.AcceptChanges();
            resultXMLReader.Close();
            Console.WriteLine("Contact Email: " + dsResult.Tables[0].Rows[0]["ContactEmail"]);
            Console.WriteLine("Name         : " + dsResult.Tables[0].Rows[0]["Name"]);
            Console.WriteLine("Number       : " + dsResult.Tables[0].Rows[0]["Number"]);
            Console.ReadLine();

            Dts.Variables["sCustomerEmailAddress"].Value = dsResult.Tables[0].Rows[0]["ContactEmail"];
            Dts.Variables["sCustomerName"].Value = dsResult.Tables[0].Rows[0]["Name"];
            Dts.Variables["sCustomerNumber"].Value = dsResult.Tables[0].Rows[0]["Number"];


            //Dts.Variables["sCustomerCode"].Value.ToString();

            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Open in new window

0
Christopher KileCommented:
Then this code should adapt perfectly.  You avoid XPATH queries and virtually all parsing of the XML itself.  If, by chance, you have a schema against which to validate this XML, you can read it into the DataSet just prior to reading the XML itself, though with a simple flat structure like this the method I supplied you is probably the simplest way of extracting the values.  

If you're going to encrypt then store the table, note this next example, where I read a second XML file into the same dataset.  Note that it added a new row to the existing table rather than overwriting the existing dataset completely.  The loop shows how to iterate through the rows of the data set, demonstrates how to modify each row and display it, and uses .AcceptChanges to insure the modfications are permanent, leaving to you whether you create a DataAdapter that will update the database for you or whether you do it yourself with SqlConnection, SqlCommand, etc.  Here is the second example:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;

namespace XMLDataSetGetSelectedValues
{
    class Program
    {
        static void Main(string[] args)
        {
            string strResult = "<ArrayOfCustomerAccount>"
                            + "<CustomerAccount>"
                            + "<ContactEmail> whacka@doo.com</ContactEmail> "
                            + "<Name>Michael Stipe</Name>"
                            + "<Number>42</Number>"
                            + "</CustomerAccount>"
                            + "</ArrayOfCustomerAccount> ";

            string strResult2 = "<ArrayOfCustomerAccount>"
                            + "<CustomerAccount>"
                            + "<ContactEmail>pot.r@z.be</ContactEmail> "
                            + "<Name>Marvin Neuman</Name>"
                            + "<Number>69</Number>"
                            + "</CustomerAccount>"
                            + "</ArrayOfCustomerAccount> ";

            // XmlTextReader resultXMLReader = new XmlTextReader(x.GetResponseStream());

            
            XmlTextReader resultXMLReader = new XmlTextReader(new StringReader(strResult));
            DataSet dsResult = new DataSet();

            dsResult.ReadXml(resultXMLReader);
            dsResult.AcceptChanges();
            resultXMLReader.Close();

            resultXMLReader = new XmlTextReader(new StringReader(strResult2));

            dsResult.ReadXml(resultXMLReader);
            dsResult.AcceptChanges();
            resultXMLReader.Close();

            for (int i = 0; i < dsResult.Tables[0].Rows.Count; i++)
            {
                dsResult.Tables[0].Rows[i]["ContactEmail"] += "; email mod";
                dsResult.Tables[0].Rows[i]["Name"] += "; name mod";
                dsResult.Tables[0].Rows[i]["Number"] += "; name mod";

                Console.WriteLine("Contact Email: " + dsResult.Tables[0].Rows[i]["ContactEmail"]);
                Console.WriteLine("Name         : " + dsResult.Tables[0].Rows[i]["Name"]);
                Console.WriteLine("Number       : " + dsResult.Tables[0].Rows[i]["Number"]);
            }
            dsResult.AcceptChanges();
            Console.ReadLine();
        }
    }
}

Open in new window


You can also initliaze the XMLTextReader straight from .GetResponsStream() (it's one of the overloads of .ReadXML()) but I leave that as an exercise for you if you want to do it that way.
0
Christopher KileCommented:
LOL our comments passed each other.  Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.