March 30, 2011 at 5:11 am
I have a SQL Server 2008 DB.
I need to create a XML file based on 6 XSD files.
I have created the views of my database which mirror the XSD files.
How do I now use SSIS to create the XML file based on these views and XSDs. Remember this is not a flat file.
There is absolutely nothing on the internet apart from companies try to get money out of you.
I can't afford to pay for some software plugin. Please help!!!! 🙂
March 30, 2011 at 5:21 am
Ignore, meant to post that in the other one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2011 at 9:28 am
Disclaimer: my approach is shaped by my lack of awareness of an SSIS-specific tool/component that can do what you're asking so read with that in mind.
Initially I thought of writing a single query that uses FOR XML and sub-selects to build an XML document in the form you want direct from the database engine. If we do it this way then we'll only need SSIS to get the XML document from the database into a file, i.e. we won't really be using any SSIS-specific functionality. That said, without more information that's as far as I can go.
If you are interested in trying the approach please post the DDL for your 6 tables, some DML to create test data in those tables and an example of your desired XML output that references your test data.
As I said, I am not aware of an SSIS component that can do what you're asking but maybe others will chime in.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 31, 2011 at 7:27 am
Here is the main xsd file.
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://www.theia.org.uk/ILR/2011-12/1" targetNamespace="http://www.theia.org.uk/ILR/2011-12/1" elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.0" id="ILR-2011-12">
<xs:include schemaLocation="ILR-HeaderDefinitions.xsd"/>
<xs:include schemaLocation="ILR-LearningProviderDefinitions.xsd"/>
<xs:include schemaLocation="ILR-LearnerDefinitions.xsd"/>
<xs:complexType name="MessageStructure">
<xs:sequence>
<xs:element name="Header" type="HeaderStructure"/>
<xs:element name="SourceFiles" minOccurs="0">
<xs:complexType>
<xs:sequence>
<xs:element name="SourceFile" type="SourceFileStructure" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="LearningProvider" type="LearningProviderStructure"/>
<xs:element name="LearnerDeletion" minOccurs="0">
<xs:complexType>
<xs:sequence>
<xs:element name=" LearnRefNumber" type="LRNType" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Learner" type="LearnerStructure" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:element name="Message" type="MessageStructure"/>
</xs:schema>
March 31, 2011 at 9:52 am
You said you had already created database objects to hold your data. Please re-read my previous post. If you're not sure what I am asking please read this article: http://www.sqlservercentral.com/articles/61537/
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply