Generating XML output using XSD structure file in SQL Server

Question

I have a quite complex XSD structure file that is supposed to help us generate a report in XML, however the XSD is prone to change and one has to keep updating the base code. Wondering if there's any way to generate XML outputs using the xsd file itself?

The XSD has a lot of nested fields

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:ns="http://-" " elementFormDefault="qualified" attributeFormDefault="unqualified" version="6-2">
<xs:include schemaLocation="--"/>
<xs:complexType name="Address_Structure">
    <xs:choice>
        <xs:element name="UnstructuredAddress" type="ns:UnstructuredAddress_Type"/>
        <xs:element name="StructuredAddress" type="ns:StructuredAddress_Type" minOccurs="2" maxOccurs="5"/>
    </xs:choice>
</xs:complexType>
<xs:complexType name="ClinicalActivity_AE_Structure">
    <xs:sequence>
        <xs:element name="ClinicalActivityGroupOPCS" type="ns:ClinicalActivityGroupOPCS_Type" minOccurs="0"/>
        <xs:element name="ClinicalActivityGroupREAD" type="ns:ClinicalActivityGroupREAD_Type" minOccurs="0"/>
        <xs:element name="ClinicalTreatmentGroupAandE" type="ns:ClinicalTreatmentGroupAAndE_Type" minOccurs="0"/>
    </xs:sequence>
</xs:complexType>
<xs:complexType name="ClinicalActivity_Structure">
    <xs:sequence>
        <xs:element name="ClinicalActivityGroupOPCS" type="ns:ClinicalActivityGroupOPCS_Type" minOccurs="0"/>
        <xs:element name="ClinicalActivityGroupREAD" type="ns:ClinicalActivityGroupREAD_Type" minOccurs="0"/>
    </xs:sequence>
</xs:complexType>
<xs:complexType name="ClinicalActivityGroupOPCS_Type">
    <xs:sequence>
        <xs:element name="ProcedureSchemeInUse" type="ns:ProcedureSchemeInUse_Type"/>
        <xs:element name="PrimaryProcedureGroupOPCS">
            <xs:complexType mixed="false">
                <xs:sequence>
                    <xs:element name="PrimaryProcedure_OPCS" type="ns:PrimaryProcedure_OPCS_Type"/>
                    <xs:element name="ProcedureDate" type="ns:ProcedureDate_Type" minOccurs="0"/>
                    <xs:element name="MainOperatingHCP" type="ns:ProfessionalsInvolved_Structure" minOccurs="0"/>
                    <xs:element name="ResponsibleAnaesthetist" type="ns:ProfessionalsInvolved_Structure" minOccurs="0"/>
                </xs:sequence>
            </xs:complexType>
        </xs:element>
        <xs:element name="SecondaryProcedureGroupOPCS" minOccurs="0" maxOccurs="unbounded">
            <xs:complexType mixed="false">
                <xs:sequence>
                    <xs:element name="SecondaryProcedure_OPCS" type="ns:Procedure_OPCS_Type"/>
                    <xs:element name="ProcedureDate" type="ns:ProcedureDate_Type" minOccurs="0"/>
                    <xs:element name="MainOperatingHCP" type="ns:ProfessionalsInvolved_Structure" minOccurs="0"/>
                    <xs:element name="ResponsibleAnaesthetist" type="ns:ProfessionalsInvolved_Structure" minOccurs="0"/>
                </xs:sequence>
            </xs:complexType>
        </xs:element>
    </xs:sequence>
</xs:complexType>

Not sure what you mean here. What are you looking for as output? — Sean Lange, Jan 30 '23 at 22:41
let's say I have a query that returns all the fields mentioned on the xsd, how do I use the XSD file to generate the XML? — Ariox66, Jan 30 '23 at 22:42
@SeanLange found this: https://stackoverflow.com/questions/35198240/how-to-export-data-from-database-to-xml-according-the-xsd It says it's not possible to automate it if it's using mixed/complex elements — Ariox66, Jan 30 '23 at 22:51
The schema can be used to generate c# classes (using xsd.exe). Then you can fill the classes from database and using xml serialization create an xml file. To make task simple the structure of the database tables should be the same as c# classes. PowerShell can be used as an alternative to c#. — jdweng, Jan 31 '23 at 04:16
@jdweng appreciate it if you could add an answer with more details and possibly some links to put me on the right path? — Ariox66, Jan 31 '23 at 05:35
@jdweng also not sure what you meant by the structure of database to the same as C# classes, the structure is not the same. would using (WITH CTE) or a Temp table help to fake the structure? — Ariox66, Jan 31 '23 at 05:38
@jdweng something like this? https://stackoverflow.com/questions/39379874/c-sharp-how-to-populate-cass-created-from-xsd-exe-from-database — Ariox66, Jan 31 '23 at 05:50
@jdweng does that mean if I populate the classes, select raw data from SQL and pass it to C# it will be automatically populated according to the original XSD with all the nested fields? — Ariox66, Jan 31 '23 at 05:51
I answered a question in 2018 where I helped developed a large SQL Database and imported data from xml. The structure was very complicated. When you have an xml with many layers you have to flatten the xml to put into a database. Xml Serialization is not always the best approach. In these cases I like to use Xml Linq. Solution included creating a sql script to create the database. to See solution here :https://stackoverflow.com/questions/50173567/unstructured-xml-via-non-tab-deliminator?force_isolation=true — jdweng, Jan 31 '23 at 10:34

Generating XML output using XSD structure file in SQL Server

0 Answers0