How do I create an xml document with multiple namespaces based on a provided schema?

How do I create an xml document with multiple namespaces based on a provided schema?
In the demonstration below, I have created an xml output using a select statement combined with a “for xml” clause.
My question is
•      “how do I get my result to include the look like the screen on the right of Figure 2, where the xsd (xml schema definition)  is integrated with the xml output?
o      Do I use the referenced schema Location?
•      Can I do integrate the result by using SSIS?
 
FIGURE 1
 CalPERS-RequestWithSoapAnd-xmlNs.pngFIGURE 2
CalPERS-RequestWithSoapAnd-xmlNs.pngCalPERS-RequestConstructedFromSQL.png
Doug KelleySenior Programmer AnalystAsked:
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.

ste5anSenior DeveloperCommented:
Basically: You don't. I don't see any sense in manually creating a SOAP response.

What ís your use case here? Why don't you setup/create your own SOAP Web Service (.NET/C#/VB.NET)?
0
Doug KelleySenior Programmer AnalystAuthor Commented:
This will be an automated process. Our requirements are to ftp a file to the outside entity whose schema is outlined in the soap envelope.

I do not yet have a commanding knowledge of xml/xsd/xslt, but have been working at attempting to understand how they work together. We are working with MS SQL 2008, SSIS, C# (VS 2010 as our API). Since a user will not be interacting with a webpage to make this happen (scheduled task) I plan to use SSIS.

I am reviewing the purpose for the xslt.Load and transform method as it related to preparing the file for sending. Not sure if I'm going down the right path.
   
//Load the XSLT style sheet specified by a path
            xslt.Load(Dts.Variables["User::FormatFilePath"].Value.ToString());
            //Transforms the XML data in the input file and outputs the result to an output file.
            xslt.Transform(Dts.Variables["User::XmlFileName"].Value.ToString(), Dts.Variables["User::XmlFileName"].Value.ToString());

Open in new window


Should I be looking into web services from the accepting company?
0
ste5anSenior DeveloperCommented:
I would do it in C# using XSD.exe to create the container classes and use the .NET built-in XML serializer.
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Doug KelleySenior Programmer AnalystAuthor Commented:
It is my understanding that the end result file to be sent should look like the enveloped file in figure 1 and 2 on the right when we are ready to ftp it.

Does this seem normal to you?
0
Doug KelleySenior Programmer AnalystAuthor Commented:
Thank you ste5an, I found out why we were "manually" creating the integrated soap/xml file. The original application was written in classic ASP and relied on SQL Server 2008 SSIS functionality to send a file that could consume the file using web services. Now that we are moving this application to an ASP.Net platform, we "could" use web services to to perform the exchange.

The solution created in SSIS used a script object that called C# to load the related xslt file and then transform a standard XML file to an integrated soap/xml file ( writes out the attribute with the specified prefix, namespace  and value.)

Here is the code block:
XslTransform xslt = new XslTransform();
            xslt.Load(Dts.Variables["User::FormatFilePath"].Value.ToString());
            xslt.Transform(Dts.Variables["User::XmlFileName"].Value.ToString(), Dts.Variables["User::XmlFileName"].Value.ToString());
            Dts.TaskResult = (int)ScriptResults.Success;            

Open in new window

The end results tranform figure 1 to figure 2
Figure 1Figure 2
0
Doug KelleySenior Programmer AnalystAuthor Commented:
Based on suggestion, we went with the same concept in SSIS. Thank you ste5an
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 2008

From novice to tech pro — start learning today.