XML population help

Hi Experts,

I posted a similar question, and I think I need to break it up a bit.  I need to create an xml template and populate it with data from a SQL 2005.  I am really a newbie when it comes to xml.  I just need to know, how to get my data in the format listed.  After my data is in there, I think I can use SSIS to generate a variable fixed length file.  The lengths of each field is located in my xml document.  Any help you can provided will be appreciated.  For now, I just need to know how to populate the data with the format listed.


data-format-in-xml.txt
MiracleByDesignAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Meir RivkinFull stack Software EngineerCommented:
can u elaborate the question?
what the xml u posted represent?
0
MiracleByDesignAuthor Commented:
I need to create an electronic W2 file known as EFW2.  The XML I posted is the file structure.  Once I get the data in that file structure, I then need to turn the xml document into a flat file which represents the "lengths" in the xml file.  So question 1 is how do I get my data to populate that xml document in the format?  My data source is an excel spreadsheet that I dumped into a SQL 2005 table. Question 2 would be how to convert the xml document into a flat file with the specific lengths I listed in the file.  If you can only answer the first question that would be helpful.

Thanks!
0
Meir RivkinFull stack Software EngineerCommented:
to answer Q1 i need to know how the sql schema looks like in terms of tables and columns.
then i can help you write a code which takes the data and convert it to the xml format you requires.
once we cross that bridge i'll help you with Q2.

0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

MiracleByDesignAuthor Commented:
The scheme of the SQL tables are just like the fields in the xml document.  Each table is titled RA, RE, RS, etc with the fields and lengths that are in the document.  I have got to have this done by tomorrow morning so if you have some general code that I can get an idea it would be most helpful!  Thanks
0
Meir RivkinFull stack Software EngineerCommented:
to sum up you have the following tables:
RA, RE, RS, RW, RT, RV, RF

each one has 3 columns:
Name, Start, Length

correct?
0
Meir RivkinFull stack Software EngineerCommented:
which language are you comfortable with?
0
MiracleByDesignAuthor Commented:
Hi Sedqwick,

Each table has the field name of what is in the Name column. So for example deliveryaddress117_138, RARecordIdentifier1_2.  I am comfortable with both C#.NET and VB.NET.

Thanks,
Mimi
0
Meir RivkinFull stack Software EngineerCommented:
where do u get the Start and Length attributes values from?
for example:
<Field Name="RARecordIdentifier1_2" Start="1" Length="2"/>

RARecordIdentifier1_2 is the field name
where the Start and Length values are stored?
0
Meir RivkinFull stack Software EngineerCommented:
i think i figured it out:
if the field name is "XXXX12_54" then the Start is 12 and Length is (54 minus 12 plus 1) = 43.

for example:
<Field Name="UserID12_19" Start="12" Length="8"/>

Start is 12 (extract from the field name itself prior the underscore character)
Length is 8 cause 19-12+1=8.

in case no number is found after the underscore character, then length is always equals 1


0
Meir RivkinFull stack Software EngineerCommented:
btw, do u use .net 3.5?
0
MiracleByDesignAuthor Commented:


Yes, I do use .net 3.5.  The Start position and length is for the flat file I need to create.  Where the data must start and the length of the field.  My first attempt at this was to create an xml file with the field names I needed and the start and length of each field in order to generate the flat file.  So what I was trying to do was populate the xml document and then take the populated document somehow read the length and starting positions and generate a flat file for my W2s.
0
Meir RivkinFull stack Software EngineerCommented:
this code snippet generate the xml according to the template you've posted.
as i explained above, the code extract the start and length attributes for the xml from the field name.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.Xml.XPath;

namespace ConsoleApplication1
{
    class Program
    {
        static readonly string DB_NAME = "";
        static readonly string SERVER_DB = "";
        static readonly string XML_TEMPLATE = "<EFW2FileMap><RA></RA><ERRecord><RE></RE><EERecord><RW></RW><RS></RS></EERecord><RT></RT><RV></RV></ERRecord><RF></RF></EFW2FileMap>";
        static KeyValuePair<string, string>[] tablesXpath = new KeyValuePair<string, string>[] 
            { 
                new KeyValuePair<string, string>("RA", "RA"),
                new KeyValuePair<string, string>("RE", "ERRecord/RE"),
                new KeyValuePair<string, string>("RS", "ERRecord/EERecord/RS"),
                new KeyValuePair<string, string>("RW", "ERRecord/EERecord/RW"),
                new KeyValuePair<string, string>("RT", "ERRecord/RT"),
                new KeyValuePair<string, string>("RV", "ERRecord/RV"),
                new KeyValuePair<string, string>("RF", "RF")
            };

        static void Main(string[] args)
        {
            string constring = string.Format("Data Source={0};Initial Catalog={1};User Id=myUsername;Password=myPassword;", SERVER_DB, DB_NAME);
            XElement xml = XElement.Parse(XML_TEMPLATE);

            using (SqlConnection sqlcon = new SqlConnection(constring))
            {
                sqlcon.Open();

                foreach (var item in tablesXpath)
                {
                    SqlCommand sqlCmd = new SqlCommand("select * from " + item.Key, sqlcon);
                    DataTable dt = GetDataTable(sqlCmd);
                    string xpath = item.Value;
                    XElement element = xml.XPathSelectElement(xpath);

                    foreach (DataRow dr in dt.Rows)
                    {
                        string field = dr["Name"].ToString();
                        int start, length;
                        ExtractFigures(field, out start, out length);

                        element.Add(new XElement("Field", new XAttribute("Name", dr["Name"].ToString()), new XAttribute("Start", start), new XAttribute("Length", length)));
                    }
                }
            }

            xml.Save(@"c:\temp\data.xml");
        }

        private static void ExtractFigures(string field, out int start, out int length)
        {
            string[] tokens = field.Split('_');
            start = 0;
            length = 1;

            for (int b = 1, i = tokens[0].Length - 1; i >= 0; i--, b *= 10)
            {
                char ch = tokens[0][i];
                if (Char.IsNumber(ch) == false) break;
                start += int.Parse(ch.ToString()) * b;
            }
            if (tokens.Length == 2)
            {
                length = int.Parse(tokens[1]) + 1;
                length -= start;
            }
        }

        public static DataTable GetDataTable(SqlCommand cmd)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataTable dtGet = new DataTable();
            da.Fill(dtGet);
            return dtGet;
        }
    }
}

Open in new window

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
Meir RivkinFull stack Software EngineerCommented:
what i basically did is to create a mapping table (array of KeyValuePair objects) which maps table name and the xpath in the xml in which i add elements in runtime.

the XML_TEMPLATE is a string represent the xml u have posted but without all the Fields nodes.
don't forget to update the DB_NAME and SERVER_DB for the connection string.

the foreach on line 35, loops through all tables and use GetDataTable() function to populate the Sql table into a .net datatable.

then for each row i take the "Name" field and extract the start and length figures for the correspondent xml attributes.
0
Meir RivkinFull stack Software EngineerCommented:
same code but without the mapping stuff (the array of KeyValuePair objects).

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.Xml.XPath;

namespace ConsoleApplication1
{
    class Program
    {
        static readonly string DB_NAME = "";
        static readonly string SERVER_DB = "";
        static readonly string XML_TEMPLATE = "<EFW2FileMap><RA></RA><ERRecord><RE></RE><EERecord><RW></RW><RS></RS></EERecord><RT></RT><RV></RV></ERRecord><RF></RF></EFW2FileMap>";
        static string[] tablesXpath = { "RA", "ERRecord/RE", "ERRecord/EERecord/RS", "ERRecord/EERecord/RW", "ERRecord/RT", "ERRecord/RV", "RF" };

        static void Main(string[] args)
        {
            string constring = string.Format("Data Source={0};Initial Catalog={1};User Id=myUsername;Password=myPassword;", SERVER_DB, DB_NAME);
            XElement xml = XElement.Parse(XML_TEMPLATE);

            using (SqlConnection sqlcon = new SqlConnection(constring))
            {
                sqlcon.Open();

                foreach (string xpath in tablesXpath)
                {
                    string table = ExtractTableName(xpath);

                    SqlCommand sqlCmd = new SqlCommand("select * from " + table, sqlcon);
                    DataTable dt = GetDataTable(sqlCmd);

                    XElement element = xml.XPathSelectElement(xpath);

                    foreach (DataRow dr in dt.Rows)
                    {
                        string field = dr["Name"].ToString();
                        int start, length;
                        ExtractFigures(field, out start, out length);

                        element.Add(new XElement("Field", new XAttribute("Name", dr["Name"].ToString()), new XAttribute("Start", start), new XAttribute("Length", length)));
                    }
                }
            }

            xml.Save(@"c:\temp\data.xml");
        }

        private static string ExtractTableName(string xpath)
        {
            string[] tokens = xpath.Split('/');
            if (tokens.Length > 1)
            {
                return tokens[tokens.Length - 1];
            }
            return xpath;
        }

        private static void ExtractFigures(string field, out int start, out int length)
        {
            string[] tokens = field.Split('_');
            start = 0;
            length = 1;

            for (int b = 1, i = tokens[0].Length - 1; i >= 0; i--, b *= 10)
            {
                char ch = tokens[0][i];
                if (Char.IsNumber(ch) == false) break;
                start += int.Parse(ch.ToString()) * b;
            }
            if (tokens.Length == 2)
            {
                length = int.Parse(tokens[1]) + 1;
                length -= start;
            }
        }

        public static DataTable GetDataTable(SqlCommand cmd)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataTable dtGet = new DataTable();
            da.Fill(dtGet);
            return dtGet;
        }
    }
}

Open in new window

0
MiracleByDesignAuthor Commented:
Thank you so much, now once the xml document is populated is there an easy way of turning it into a flat file with the field lengths and the start positions?
0
Meir RivkinFull stack Software EngineerCommented:
yes there is, i just need an example of how it should look like.
just post lets say 2 fields and describe the usage of the length and start attributes
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
XML

From novice to tech pro — start learning today.