Solved

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

Posted on 2014-12-15
6
183 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now