Solved

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

Posted on 2014-12-15
6
208 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 34

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 34

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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