Re: Exporting SQL Server data to XML

I don't think there's a built in way to create XML that conforms to a known schema. I would just use a FOR XML PATH query and hand craft it. Can be difficult, I know.
There are tools that can show you what the instance document should look like if you don't understand XML Schema documents. (Visual Studio 2008 SP1 for example, load the schema, right click the root element and create an instance.)

--

Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"Amir Tohidi" <AmirTohidi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:7D3C21E0-A540-4914-A452-86EBD065C3AE@xxxxxxxxxxxxxxxx
Hello

Can anyone please suggest the best way to address the following problem (I
did Google, but couldn't find anything):

I have been given a SQL Server database and an XSD file. The requirement is
to export the SQL Server data into an XML document that conforms to the
supplied XSD.

I am .NET / SQL Server developer, yet I have not previously had to do
anything like this. More importantly, I have not done any XSD stuff before.

Does SQL Server / .NET Framework help with this? I need the simplest
solution possible please as time is of the essence.

Thanks
Amir

Excerpt from the XSD:


<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSpy v2008 sp1 (http://www.altova.com) by Richard
(Department For Education & Skills) -->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema";
xmlns:bs7666="http://www.govtalk.gov.uk/people/bs7666";
xmlns:core="http://www.govtalk.gov.uk/core";
<xs:element name="ParticipantInformation">
<xs:annotation>
<xs:documentation>ESF2007 Participant training event
information</xs:documentation>
<xs:appinfo>
<Creator>Project ESF2007</Creator>
<Created>2008-05-20</Created>
<Modified>2008-05-22</Modified>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="ProvidingOrganisation">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="TransferDate" type="core:DateType"/>
<xs:element ref="Region" maxOccurs="unbounded"/>
</xs:sequence>
<xs:attribute name="SchemaVersion" type="xs:NMTOKEN" use="required"
fixed="0.3"/>
</xs:complexType>
</xs:element>
<xs:element name="Region">
<xs:complexType>
<xs:sequence>
<xs:element name="RegionCode">
<xs:simpleType>
<xs:restriction base="RegionCodeType"/>
</xs:simpleType>
</xs:element>
<xs:element name="RegionName">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element ref="Agreement" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>



.

Relevant Pages

  • Re: XML Literals in VB9 - Whats wring with this?
    ... As for generating xsd scheme, .net framework does provide a tool "xsd.exe", ... it can help you generate xsd from a given sample XML document or generate ... However, since it's code logic is quite simple, the generated schema or ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SQLXMLBulkLoad of text child nodes vs. attributes?
    ... You could use the XSDInference tool to get a XSD schema from xml. ... annotations to make it a proper sqlxml schema. ...
    (microsoft.public.sqlserver.xml)
  • Re: Simple mapping question
    ... Yeap created the XSD for the source and destination, ... The destination schema in your case should be a flat file schema. ... There isn't an XML schema ... ...
    (microsoft.public.biztalk.general)
  • Re: how to return xml document from a web service
    ... what specific XML you expect. ... If you have a schema that defines what you expect, ... The second issue with this approach is that XML is not a string. ... >> methods from the wire transport. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: Mapping is not working
    ... What I figured out was that two of the data elements in the XML Schema ... file that is the same schema as what SQL Server generates and I dump ... It looks like how the namespace is set up. ...
    (microsoft.public.biztalk.general)
Loading