Convert multiple tables to xml file

Question

I have this data in Sql Server that I need to convert to XML files, one per Customer. I have 2 sql queries, Customers and Messages.

I also have an XSD file that was given as a requirement.

What's the most efficient way to generate this particular XML file?

UPDATE: By efficient, I mean either fast processing, or simple to code. I'm not too concerned about speed because they are small files. Thanks.

This is the sample xml that was given to me:

<?xml version="1.0" encoding="UTF-8"?>
<Program xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="program.xsd">
    <Customer>
        <CId>6674</CId>
        <ClientName>c1</ClientName>
        <SubscriberId>1234</SubscriberId>
    </Customer>
    <CreatedOn>2014-06-17T19:09:53.960</CreatedOn>
    <Message>
        <MessageId DeliveryChannel="1">123456</MessageId>
        <Prospect>
            <Id>12345678</Id>
        </Prospect>
        <SentDate>2014-06-24T12:00:01</SentDate>
        <CName>x1</CName>
        <CNameId>1234</CNameId>
    </Message>
    <Message>
        <MessageId DeliveryChannel="2">1236457</MessageId>
        <Prospect>
            <Id>12345679</Id>
        </Prospect>
        <SentDate>2014-06-24T12:00:02</SentDate>
        <CName>x2</CName>
        <CNameId>1235</CNameId>
    </Message>
</Program>

Schema:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> 

<xs:element name="Program"> 
<xs:complexType> 
<xs:sequence> 
<xs:element ref="Customer" minOccurs="1" maxOccurs="unbounded"/>
<xs:element name="CreatedOn" type="xs:dateTime" minOccurs="1" maxOccurs="1"/> 
<xs:element ref="Message" minOccurs="1" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Customer">
<xs:complexType>
<xs:sequence>
<xs:element name="CId" type="xs:string"  minOccurs="1" maxOccurs="1"/> 
<xs:element name="ClientName" type="xs:string"  minOccurs="1" maxOccurs="1"/> 
<xs:element name="SubscriberId" type="xs:integer" minOccurs="1" maxOccurs="1"/> 
</xs:sequence> 
</xs:complexType>
</xs:element>


<xs:element name="Message">
<xs:complexType>
<xs:sequence>
<xs:element ref="MessageId" minOccurs="1" maxOccurs="1"/>
<xs:element ref="Prospect" minOccurs="1" maxOccurs="1"/> 
<xs:element name="SentDate" type="xs:dateTime" minOccurs="1" maxOccurs="1"/> 
<xs:element name="CName" type="xs:string" minOccurs="1" maxOccurs="1"/> 
<xs:element name="CNameId" type="xs:integer" minOccurs="0" maxOccurs="1"/>
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Prospect">
<xs:complexType>
<xs:sequence>
<xs:element ref="Id" minOccurs="1" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Id">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="Source" type="xs:string" default="default"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>

<xs:simpleType name="DeliveryChannel">
<xs:restriction base="xs:integer">
<xs:enumeration value="1"/>
<xs:enumeration value="2"/>
<xs:enumeration value="4"/>
</xs:restriction>
</xs:simpleType>

<xs:element name="MessageId">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:integer">
<xs:attribute name="DeliveryChannel" type="DeliveryChannel"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>


</xs:schema>

To the person who downvoted, I guess you don't know the answer either eh? Much easier to be unuseful. — live-love, Jun 24 '14 at 21:24
Could you also post the table schema? I cant give you an example query without the schema, and specially without knowing how the tables are related — Adrian Salazar, Jun 24 '14 at 21:27
What do you mean by efficient? Computationally? Ease of code? I would think generating C# files from the XSD would be very efficient. — TheNorthWes, Jun 24 '14 at 21:30
I found my answer in this thread: http://stackoverflow.com/questions/284324/what-is-the-best-way-to-build-xml-in-c-sharp-code — live-love, Jun 25 '14 at 13:19

score 1 · Answer 1 · answered Jun 24 '14 at 21:47

Are you open to another language by any chance? VB.Net has some really nice built-in XML support that produces valid XML without having to play around with strings too much:

' Customers would be a database context of some form
For Each C In Customers
    Dim MyData = <?xml version="1.0" encoding="UTF-8"?>
                 <Program xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="program.xsd">
                     <Customer>
                         <CId><%= C.CId %></CId>
                         <ClientName><%= C.ClientName %></ClientName>
                         <SubscriberId><%= C.SubscriberId %></SubscriberId>
                     </Customer>
                     <CreatedOn><%= C.CreatedOn %></CreatedOn>
                     <Message>
                         <MessageId DeliveryChannel=<%= C.DeliveryChannel %>>123456</MessageId>
                         <Prospect>
                             <Id>12345678</Id>
                         </Prospect>
                         <SentDate>2014-06-24T12:00:01</SentDate>
                         <CName>x1</CName>
                         <CNameId>1234</CNameId>
                     </Message>
                     <Message>
                         <MessageId DeliveryChannel="2">1236457</MessageId>
                         <Prospect>
                             <Id>12345679</Id>
                         </Prospect>
                         <SentDate>2014-06-24T12:00:02</SentDate>
                         <CName>x2</CName>
                         <CNameId>1235</CNameId>
                     </Message>
                 </Program>
    MyData.Save("filename here", SaveOptions.None)
Next

score 0 · Accepted Answer · answered Jun 27 '14 at 19:54

I decided to use XmlWriter to write this XML. Code:

    private static SqlDataReader GetData(SqlConnection sqlConn, int subscriberID, DateTime fromDate, DateTime toDate)
    {
        SqlDataReader rdr;
        using (System.Data.SqlClient.SqlCommand cmd = new SqlCommand("GetData", sqlConn)) 
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SubscriberId", subscriberID);
            cmd.Parameters.AddWithValue("@FromDate", fromDate);
            cmd.Parameters.AddWithValue("@ToDate", toDate);
            rdr = cmd.ExecuteReader();  
        }
        return (rdr);
    }


    private static string WriteXml(SqlConnection sqlConn)
    {
        string xmlOutput = "";
        System.Data.SqlClient.SqlDataReader rdrDealers = GetDealers(sqlConn);
        {
            while (rdrDealers.Read())
            {
                using (SqlDataReader rdrCalls = GetData(...)
                {
                    if (rdrCalls.HasRows)
                    {
                        XmlWriterSettings wSettings = new XmlWriterSettings();
                        wSettings.Indent = true;
                        MemoryStream ms = new MemoryStream();
                        XmlWriter xw = XmlWriter.Create(ms, wSettings);

                        xw.WriteStartDocument();
                        xw.WriteStartElement("program");
                        xw.WriteAttributeString("xmlns", "xsi", null, "http://www.w3.org/2001/XMLSchema-instance");
                        xw.WriteAttributeString("xsi", "noNamespaceSchemaLocation", null, "program.xsd");

                        {
                            string CContractId = Convert.ToString(rdrDealers.GetInt32(1));
                            string clientName = rdrDealers.GetString(2);

                            xw.WriteStartElement("Customer");
                            xw.WriteStartElement("CContractId");
                            xw.WriteString(CContractId);
                            xw.WriteEndElement();
                            xw.WriteStartElement("ClientName");
                            xw.WriteString(clientName);
                            xw.WriteEndElement();
                            xw.WriteStartElement("SubscriberId");
                            xw.WriteString(Convert.ToString(subscriberID));
                            xw.WriteEndElement();
                            xw.WriteEndElement();
                            xw.WriteStartElement("CreatedOn");
                            xw.WriteString(DateTime.UtcNow.ToString("o"));
                            xw.WriteEndElement();

                            while (rdrCalls.Read())
                            {
                                string messageID = Convert.ToString(rdrCalls[0] as int?);  // rdrCalls[0] as string;
                                string deliveryChannelID = Convert.ToString(rdrCalls[1] as int?); //Convert.ToString(rdrCalls.GetInt32(1));
                                string sentTms = (rdrCalls[2] as DateTime? ?? default(DateTime)).ToUniversalTime().ToString("o");
                                string campaignName = rdrCalls[3] as string;
                                string prospectID = rdrCalls[4] as string;
                                string myCampaignID = rdrCalls[5] as string;

                                xw.WriteStartElement("Message");
                                xw.WriteStartElement("MessageId");
                                xw.WriteAttributeString("DeliveryChannel", Convert.ToString(deliveryChannelID));
                                xw.WriteString(messageID);
                                xw.WriteEndElement();
                                xw.WriteStartElement("Prospect");
                                xw.WriteStartElement("Id");
                                xw.WriteString(prospectID);
                                xw.WriteEndElement();
                                xw.WriteEndElement();
                                xw.WriteStartElement("SentTms");
                                xw.WriteString(sentTms);
                                xw.WriteEndElement();
                                xw.WriteStartElement("CampaignName");
                                xw.WriteString(campaignName);
                                xw.WriteEndElement();
                                xw.WriteStartElement("myCampaignId");
                                xw.WriteString(myCampaignID);
                                xw.WriteEndElement();
                                xw.WriteEndElement();
                            }
                        }

                        xw.WriteEndDocument();
                        xw.Flush();
                        Byte[] buffer = new Byte[ms.Length];
                        buffer = ms.ToArray();
                        xmlOutput = System.Text.Encoding.UTF8.GetString(buffer);

                        string filePath = ...
                        File.WriteAllText(filePath, xmlOutput);

                        using (WebClient client = new WebClient())
                        {
                            client.UploadFile(url, filePath);
                        }
                    }
                }
            }
        }
        return xmlOutput;
    }

I think you'd be better off if you either: * generated the XML SQL Server side, FOR XML, or * used XDocument and XElement from System.Xml.Linq, or * used LinqToXSD. — Darek, Jun 27 '14 at 19:58

Convert multiple tables to xml file

2 Answers2