Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-12-15
6
Medium Priority
?
213 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 35

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 35

Accepted Solution

by:
ste5an earned 1500 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

610 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