Solved

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

Posted on 2014-12-15
6
198 Views
Last Modified: 2016-02-15
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
0
Comment
Question by:Doug Kelley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40502444
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
 

Author Comment

by:Doug Kelley
ID: 40503841
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
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40504298
I would do it in C# using XSD.exe to create the container classes and use the .NET built-in XML serializer.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:Doug Kelley
ID: 40505410
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
 

Author Comment

by:Doug Kelley
ID: 40509862
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
 

Author Closing Comment

by:Doug Kelley
ID: 40509869
Based on suggestion, we went with the same concept in SSIS. Thank you ste5an
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question